SQL interview questions that reveal real query skill

The SQL interview questions that actually predict performance test reasoning, not recall. Anyone can memorise that LEFT JOIN keeps unmatched rows. The signal is whether a candidate picks the right join for the question, explains why a query is slow, and handles the NULL that breaks their COUNT. This page gives you 16 SQL interview questions grouped by what they measure, with what a strong answer shows and the red flags, plus a rubric to score them the same way for every candidate.

A quiz can check whether someone knows syntax. It can't watch them reason through an ambiguous request or debug their own query out loud, which is where real data skill shows. So treat these as prompts for a structured, scored conversation, not a trivia test. Score each answer against the rubric line, not against how fast they typed.

Key Takeaways

• Strong SQL candidates reason about which construct fits the question (join type, aggregation, window function), not just recall syntax.

• Group questions by skill: joins, aggregation, window functions, subqueries and CTEs, NULL and data-quality handling, and performance.

• The highest-signal moment is debugging: ask why a query is slow or wrong and watch them reason, not recite.

• Score every answer against a written rubric (correctness, efficiency, and clarity of reasoning) so candidates are actually comparable.

• NULL handling and accidental row multiplication from joins are the two mistakes that separate confident-but-wrong from genuinely strong.

How to use these questions

Pick five or six across the skill areas below, ask them the same way to every candidate, and score each answer right after against a rubric. Structured, scored interviews predict performance at about 0.51 validity versus 0.20 for an unstructured chat (Plum, on Schmidt and Hunter). The structure is the signal; the question is just the prompt.

Each question lists what it tests, what a strong answer shows (your rubric), and the red flags. Keep code expectations conversational: a candidate explaining why they'd use a window function is often more telling than whether they nail the exact syntax under pressure.

Joins and row multiplication

Joins are where confident candidates quietly produce wrong numbers.

When would you use a LEFT JOIN instead of an INNER JOIN, and what's the risk?

• What it tests: whether they understand result-set shape, not just keywords.

• A strong answer shows: LEFT JOIN preserves unmatched left-side rows, and they flag that downstream aggregates can be wrong if they forget the resulting NULLs.

• Red flags: they recite the definition but can't say when it matters, or they think joins never change row counts.

A report's revenue total doubled after you added a join. What happened?

• What it tests: debugging instinct and understanding of join cardinality.

• A strong answer shows: they immediately suspect a one-to-many join multiplying rows, and they'd check the join key's uniqueness or pre-aggregate before joining.

• Red flags: they blame the data, or have no theory for why a join inflates a sum.

How would you find rows in table A that have no match in table B?

• What it tests: anti-join fluency, a common real task.

• A strong answer shows: a LEFT JOIN ... WHERE b.id IS NULL or a NOT EXISTS, and they can say why NOT IN is risky with NULLs.

• Red flags: they only know NOT IN and don't know its NULL trap.

Aggregation and GROUP BY

What's the difference between WHERE and HAVING?

• What it tests: understanding of query execution order.

• A strong answer shows: WHERE filters rows before aggregation, HAVING filters groups after, and they pick the right one for performance.

• Red flags: they use HAVING for everything, or think the two are interchangeable.

Write the logic to get the top customer by revenue per region.

• What it tests: per-group "top N", a genuinely common request that trips people up.

• A strong answer shows: they reach for a window function (ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)) rather than a fragile GROUP BY plus self-join.

• Red flags: they GROUP BY and grab MAX(revenue) but can't return the customer who had it.

How do you count distinct values, and when is that expensive?

• What it tests: awareness that correctness and cost interact.

• A strong answer shows: COUNT(DISTINCT col), plus they note it can be heavy at scale and mention approximate-count approaches when exactness isn't required.

• Red flags: no awareness that DISTINCT over large data has a cost.

Window functions

Window functions are the clearest line between intermediate and strong SQL.

Explain a window function in plain language, with one use case.

• What it tests: whether they actually understand them or just pasted one once.

• A strong answer shows: a calculation across a set of rows related to the current row without collapsing them, with a real example (running total, rank, moving average).

• Red flags: they conflate it with GROUP BY, or can't produce a use case.

How would you calculate a running total of daily sales?

• What it tests: applied window-function skill.

• A strong answer shows: SUM(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

  and an understanding of the frame clause (the PostgreSQL window-function reference is a good calibration point for your own bar).

• Red flags: they try to do it with a correlated subquery and don't know why it's slow.

What's the difference between ROW_NUMBER, RANK, and DENSE_RANK?

• What it tests: precision, which matters in real reporting.

• A strong answer shows: how each handles ties, and a case where choosing wrong changes the result.

• Red flags: they treat all three as the same.

Subqueries, CTEs, and readability

When would you use a CTE instead of a subquery?

• What it tests: judgment about readability and maintainability, not just correctness.

• A strong answer shows: CTEs (WITH ...) for readable, multi-step logic, and awareness that they don't automatically improve performance.

• Red flags: they believe CTEs are always faster, or never use them and write deeply nested subqueries.

Walk me through how you'd break a complex reporting query into steps.

• What it tests: how they manage complexity, which predicts maintainable work.

• A strong answer shows: staged CTEs, each named for what it does, validated independently.

• Red flags: one giant unreadable query with no decomposition.

NULLs and data-quality edge cases

This is where careful engineers separate from careless ones.

What does COUNT(*)return versus COUNT(column)when the column has NULLs?

• What it tests: a precise, high-consequence detail.

• A strong answer shows: COUNT(*) counts all rows, COUNT(column) skips NULLs, and they know this silently breaks metrics.

• Red flags: they assume the two are identical.

How do you safely handle NULLs in a calculation?

• What it tests: defensive querying.

• A strong answer shows: COALESCE or IS NULL checks, and they explain how NULL propagates through arithmetic and comparisons.

• Red flags: they treat NULL like zero or an empty string without thinking.

How would you find and handle duplicate rows?

• What it tests: real data-cleaning skill.

• A strong answer shows: GROUP BY with HAVING COUNT(*) > 1 to find them, and a deliberate de-dup using ROW_NUMBER(), plus a question about why the duplicates exist.

• Red flags: they delete duplicates without checking the source of the problem.

Set operations, CASE logic, and dates

These come up constantly in real reporting work and quietly trip people up.

What's the difference between UNION and UNION ALL, and which should you default to?

• What it tests: whether they understand the hidden cost of deduplication.

• A strong answer shows: UNION removes duplicates (and pays a sort or hash cost to do it), UNION ALL keeps everything and is cheaper, and they default to UNION ALL unless dedup is actually required.

• Red flags: they always reach for UNION and never consider the cost.

How would you bucket values into categories inside a query?

• What it tests: fluency with conditional logic.

• A strong answer shows: a CASE WHEN expression, often inside an aggregate, and they know it can drive pivot-style summaries.

• Red flags: they pull the data out and bucket it in application code when SQL would do it in one pass.

How do you count events per day, including days with zero events?

• What it tests: a classic gap-filling problem most people get wrong.

• A strong answer shows: they generate a complete date series and LEFT JOIN the events onto it, rather than grouping only over days that happen to have rows.

• Red flags: they GROUP BY date and don't notice the missing zero-rows until the chart looks wrong.

What's a common mistake when filtering on a timestamp column by date?

• What it tests: an everyday correctness trap.

• A strong answer shows: comparing a timestamp to a bare date misses part of the day, so they use a half-open range ( >= start AND < next_day) and think about time zones.

• Red flags: WHERE created_at = '2026-05-19' and no awareness it silently drops rows.

Data modeling judgment

When would you denormalise on purpose?

• What it tests: whether they treat normalization as dogma or as a trade-off.

• A strong answer shows: read-heavy reporting or performance-critical paths where a controlled duplication beats repeated joins, with awareness of the write-time cost.

• Red flags: "always normalise" or "always denormalise", with no sense of the trade-off.

How would you store a one-to-many relationship, and how does that change your queries?

• What it tests: foundational modeling that predicts whether their joins will be correct.

• A strong answer shows: a foreign key on the many side, and they connect the model back to the row-multiplication risk from earlier.

• Red flags: they'd stuff the many side into a delimited string column.

Performance and reasoning under the hood

A query that ran fine last month is now slow. How do you investigate?

• What it tests: the single most useful real-world skill, debugging.

• A strong answer shows: they read the query plan (EXPLAIN), check whether data volume or a missing index changed, and reason about what the planner is doing.

• Red flags: they guess and start adding indexes blindly.

When does adding an index hurt rather than help?

• What it tests: depth beyond "indexes make things fast."

• A strong answer shows: write-heavy tables, low-selectivity columns, and the storage and maintenance cost of indexes.

• Red flags: they think more indexes are always better.

A join between two large tables is slow. Walk me through how you'd speed it up.

• What it tests: applied performance reasoning, not memorised tips.

• A strong answer shows: they check for indexes on the join keys, reduce row counts before the join (filter or pre-aggregate early), read the plan to see the join strategy, and ask whether the join is even necessary.

• Red flags: they jump straight to "add more RAM" or spray indexes without reading the plan.

What does a database transaction guarantee, and when have you needed one?

• What it tests: data-integrity awareness, which separates app-level thinking from data thinking.

• A strong answer shows: atomicity across multiple statements, a real example (a transfer or an inventory decrement), and awareness that isolation levels are a trade-off.

• Red flags: they've never used one deliberately, or they think every single query needs a transaction.

Scoring SQL answers without guessing

Score each answer on three things: correctness (does the logic return the right result), efficiency (do they reason about cost at scale), and clarity (can they explain why). Use a simple four-level rubric per question, write the score immediately, and cite the specific thing the candidate said or wrote. Two interviewers more than a level apart is a calibration conversation, not an average.

This is the gap an AI interviewer for backend developers closes for data and backend roles. It asks the same SQL set, lets the candidate write and run real queries, and scores correctness, efficiency, and reasoning against the rubric with the transcript behind every score. A timed multiple-choice skills test can't watch someone debug a slow query; a structured, scored conversation can. The scoring methodology walks through how a rubric becomes a defensible number.

Want to see it before you run it on a candidate? Practice an AI interview and read the scorecard, hand candidates a free AI mock interview so they arrive ready, or pair these with the system design interview questions for a fuller backend round.

Frequently asked questions

What are the most important SQL interview questions? The ones that test reasoning over recall: choosing the right join, per-group top-N with window functions, NULL handling, and debugging a slow query. Syntax trivia predicts far less than how someone reasons about result-set shape and cost.

What level of SQL should I test for? Match it to the role. For most data and backend roles, joins, aggregation, window functions, and basic performance reasoning are the core. Reserve deep query-tuning for senior data-engineering roles.

How do you score a SQL interview fairly? Use a written rubric scoring correctness, efficiency, and clarity of reasoning, ask every candidate the same questions, and score immediately against the rubric rather than on a gut feel about speed.

Should SQL interviews use live coding or take-homes? Live querying reveals reasoning and debugging that a polished take-home hides, but it should be a real environment, not whiteboard SQL. The strongest signal is watching a candidate fix their own wrong query.

How can candidates prepare for SQL interviews? Practise explaining why you'd use a construct, not just writing it. Run through joins, window functions, and NULLedge cases out loud. A scored mock run shows you where your reasoning is thin before it counts.

Build a SQL round you can defend

The best SQL interview questions surface reasoning: which construct fits, why a query is slow, and what happens when the data is messy. Pick five or six across joins, aggregation, window functions, and performance, ask them the same way every time, and score each against a rubric for correctness, efficiency, and reasoning.

If you'd rather not run and score that round by hand for every candidate, that's the job Expert Hire does. See a sample candidate scorecard with the query, the rubric, and the reasoning per score, and decide whether it matches your bar. Browse the full question bank for the rest of the round.

• By the Expert Hire team. Last updated May 19, 2026.