SQL interview questions

The patterns that repeat

The traps

• **COUNT(*) versus COUNT(column).** The second skips nulls. Interviewers use this deliberately.
• NOT IN with nulls. If the subquery returns a null, NOT IN returns nothing. Use NOT EXISTS.
• WHERE versus HAVING. Filtering an aggregate in WHERE will not work, and filtering a row in HAVING wastes work.
• Join multiplication. A one-to-many join inflates your sums. If a total looks too high, this is usually why.
• Ties. RANK, DENSE_RANK and ROW_NUMBER differ precisely when there are ties, and the question usually has ties.

The optimisation follow-up

After you produce a working query, expect "this is slow on a billion rows - what now?" A good answer walks through:

• Read the plan first. Say you would look at the execution plan rather than guessing.
• Indexes on the join and filter columns, and why a composite index's column order matters.
• Partition pruning - is the query able to skip partitions, or is the filter written in a way that defeats it?
• Avoid functions on indexed columns in WHERE, which stops the index being used.
• Reduce before you join. Filter and aggregate early rather than joining everything and filtering at the end.
• Materialising an intermediate result when it is reused several times.

How to practise

Write the eight patterns from memory, without reference, until each takes under two minutes. That is a far better use of time than solving fifty varied problems, because the patterns are what transfer. Then practise saying your approach out loud before typing, since most SQL rounds are live and the interviewer is listening to your reasoning.

Where CoPilot Interview fits

Disclosure: we build an interview assistant, so treat this as a disclosed pitch. CoPilot Interview is a native Windows and macOS app that puts suggested answers on your own screen in about two seconds, across coding, system design and behavioral rounds, with a permanent free tier that needs no credit card.

There is a role page at SQL interview help. The roles that lean hardest on SQL are covered in data engineer interview questions and data analyst interview questions.

Frequently asked questions

What SQL questions are asked in interviews?

Around eight patterns cover most of them: top N per group, deduplication, running totals, period comparison with LAG, gaps and islands, conditional aggregation, self-joins and anti-joins. Four are window functions, which is why window functions are the highest-value thing to drill.

What level of SQL is needed for interviews?

Comfortable with window functions, multiple joins and subqueries, written live without reference. Most candidates can read these constructs but cannot produce them quickly under observation, which is what the round actually tests.

How do you answer the 'make this query faster' question?

Say you would read the execution plan first rather than guessing, then cover indexes on join and filter columns, partition pruning, avoiding functions on indexed columns in WHERE, and filtering or aggregating before joining rather than after.

What is the most common SQL interview mistake?

Join multiplication - a one-to-many join silently inflating aggregates. If a total looks implausibly high, that is almost always the cause. Close behind is NOT IN returning nothing when the subquery contains a null.