How to actually pass the Provyn SQL assessment

What the assessment is actually testing

The Provyn SQL assessment is built around the kind of work backend and data engineers do day-to-day. It is not a trivia quiz. Most questions ask you to either write SQL that returns a specific result, identify the bug in a query that almost works, or pick the right index strategy for a given access pattern.

About 40 percent of the points are auto-graded MCQ. The rest are short-answer or live SQL execution where a senior reviewer reads what you wrote. That means partial credit is real — explain your reasoning in comments and you will earn points even if your final query is off by one.

The four topics that cost the most points

Window functions are the highest-value area. Every Provyn SQL attempt has at least two window-function questions, and they are weighted heavier than basic SELECT questions. Make sure you can fluently write OVER (PARTITION BY ... ORDER BY ...) and use ROW_NUMBER, RANK, LAG, and LEAD.

Joins are second. Specifically, knowing when to use a LEFT JOIN versus an EXISTS subquery, and recognizing the patterns where INNER JOIN silently drops rows you cared about. The assessment will test you on a self-join at least once.

Indexing strategy is third. You will be shown a query and asked which composite index would best serve it, or asked to read an EXPLAIN plan and identify the missing index. Memorize the rule of thumb: leading column of a composite index must be in your WHERE clause for the index to be used.

Transactions are fourth. You will see at least one question on isolation levels — usually a scenario where READ COMMITTED produces a wrong answer that REPEATABLE READ would fix.

A four-day prep schedule

Day one: do the practice mode in the runner. It takes 15 minutes and shows you the question types without burning your real attempt. Use it to calibrate how fast you need to go.

Day two: drill window functions on Mode Analytics or DataLemur. Pick 10 medium-difficulty problems and time yourself. If any take more than 8 minutes, that pattern needs more work before the real assessment.

Day three: read a real EXPLAIN plan from the database you use most. Postgres or MySQL — both are fair game. Look up the index types you do not recognize.

Day four: review your wrong answers from days one through three. The Provyn assessment reuses a common pool of question patterns. If you saw a SELF-JOIN drill on Tuesday, you will see something structurally similar on the real test.

What graders look for in the live SQL questions

Reviewers are senior data engineers. They are reading your code the same way they would in a code review. Things that earn full credit even when the final output is slightly wrong:

Correct join cardinality. If the question asks for one row per customer and your query produces one row per customer-order pair, that is a wrong shape no matter how clean the SQL is.

Reasonable index assumptions. If you write a query that requires a covering index that does not exist in the schema you were shown, call it out: a comment that says "would need an index on (status, created_at) to be fast" is worth more than a clever query that ignores the issue.

Comments on intentional trade-offs. "Using DISTINCT here even though it is slower than GROUP BY because the read pattern is rare and clarity matters" — that earns full points.

Mistakes that cost the most points

Forgetting to handle NULLs. Every SQL question has at least one NULL trap. If you write a JOIN without thinking about whether the right side might be NULL, expect to lose 5-10 points.

Using SELECT *. The runner shows you the schema. If the question asks for specific columns, don't lazily SELECT * — graders treat it as a flag for inattention.

Skipping comments. Manual-review questions without any rationale read as 'I guessed.' Even one line — "I'm using a CTE here for readability" — is worth points.

Misreading the prompt. The question wording is exact. If it says "per region", you need PARTITION BY region. If it says "only the most recent", you need a window function or a self-join.

What happens when you pass

Once you pass, the credential lands on your Provyn profile within 30 minutes. You get a verified badge with your score, percentile, and mastery level (Apprentice, Practitioner, or Mastery depending on how high you scored).

The credential URL is durable — recruiters can verify it without a Provyn account. You can paste it on your resume, drop the embeddable badge on your personal site, or include it in any future job application as a single source of truth.