The Query Interview Drill

Interviewers rarely invent a new question. They pick from a small set of shapes that test whether you can group, filter, rank and join without confusing the four. Practise recognising the shape first, because the syntax is the easy half.

The six shapes

1. Top N per group. The highest-paid employee in each department. ROW_NUMBER in a subquery, filtered outside.
2. Second or nth value. The second-highest salary. The same pattern with a different filter, or DENSE_RANK if ties should share a place.
3. Rows with no match. Customers who never ordered. LEFT JOIN with an IS NULL test, or NOT EXISTS.
4. Duplicates. Emails appearing more than once. GROUP BY with HAVING COUNT(*) > 1.
5. Running or cumulative totals. Revenue to date by month. SUM with an ORDER BY inside the window.
6. Period-on-period change. This month against last. LAG, or a self join on a date offset.

Two patterns worth memorising

Rows with no match

SELECT c.id, c.name
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE  o.id IS NULL;

Duplicates

SELECT email, COUNT(*) AS n
FROM   users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

Talk before you type

The person watching is assessing how you think at least as much as what you produce. Say which tables you need and how they join, state the grain of your answer in one sentence, then write. If you are unsure whether duplicates are possible in a key, ask. Asking reads as care; discovering it silently does not.

• Restate the question in terms of rows: "one row per customer per month".
• Name the join key and say whether you expect it to be unique.
• Write the query in the order it executes: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

The follow-up you should expect

Almost every round ends with the same question in some form: how would this behave on a much larger table. You do not need to be a performance specialist. Mention that you would check whether the join and filter columns are indexed, avoid a function on a column in a WHERE clause because it prevents index use, and select only the columns you need.