By the end of this lesson you can tackle an unfamiliar SQL interview question with a five-step method: ask clarifying questions, look at the data, build the query in small verified steps, narrate your reasoning and test NULLs, ties and empty groups.
Why it matters: Most candidates who fail SQL interviews know the syntax. They fail because they answer a different question from the one asked, miss an edge case the interviewer planted, or go silent for ten minutes. A method fixes all three.
Dataset: Wadi Market online store. Examples run in the editor on the right.
A SQL interview is not a syntax quiz. The interviewer wants to see how you turn a vague business request into a correct, readable query, and whether they could trust you with their data. You will usually meet one or more of these formats:
- Live coding (30–60 minutes): a shared editor or whiteboard, two to five questions of rising difficulty, the interviewer watching and asking follow-ups.
- Online assessment: timed questions graded automatically against hidden data, exactly like the exercises in SQL Quest. Hidden data means hard-coded answers fail.
- Take-home: a dataset and a business brief, a few days to answer, followed by a call where you walk through your work.
- Query review: you are shown someone else's query and asked what is wrong with it, or what it returns.
The schema will be unfamiliar. That is deliberate: the interviewer is testing how quickly you can read tables you have never seen. This track rotates through all of SQL Quest's datasets for the same reason.
The five-step method
- Clarify. Restate the question in your own words and ask what is ambiguous: What counts as revenue? Which time period? Are cancelled orders included? What should happen with ties? Should customers with no orders appear with 0? Every one of these changes the answer.
- Explore. Look at the tables before writing the final query: row counts, the distinct values of status columns, a few sample rows, whether the key you plan to join on is unique.
- Build in steps. Write the query in layers (usually CTEs): filter, then join, then aggregate, then rank. Run each layer and check its row count before stacking the next.
- Think aloud. Say what you are doing and why: “I'm joining payments, which is one-to-many, so I'll aggregate payments per order first to avoid double counting.” Silence makes a correct answer look like luck; narration makes even a slip look like a professional catching their own mistake.
- Test the edges. Before you say done, check NULLs, ties, empty groups, duplicates and the boundaries of date ranges. Then state your assumptions out loud.
SELECT status, COUNT(*) AS payments
FROM payments
GROUP BY status
ORDER BY payments DESC;One quick GROUP BY shows that payments are not all money received: there are captured, failed and refunded rows. Summing amount without a status filter would count failed attempts as revenue. Thirty seconds of exploring prevents the most common wrong answer to a revenue question.
SELECT COUNT(*) AS payment_rows,
COUNT(DISTINCT order_id) AS orders_with_payments
FROM payments;There are more payment rows than distinct orders, so some orders have several payments (a failed attempt then a success, or a split payment). Joining orders to payments therefore multiplies order rows. You now know to aggregate or filter payments before combining them with order lines.
Testing edge cases
Interviewers plant the same handful of traps in almost every dataset. Check for each of them out loud:
| Edge case | Question to ask yourself | Typical fix |
|---|---|---|
| NULLs | Can this column be empty? Does <>, NOT IN or COUNT(col) silently drop those rows? | IS NULL, COALESCE, NOT EXISTS, COUNT(*) vs COUNT(col) |
| Ties | If two rows share the top value, should both appear? | RANK/DENSE_RANK instead of LIMIT or ROW_NUMBER |
| Empty groups | Should a product with no sales show as 0? | LEFT JOIN from the full list + COALESCE(…, 0) |
| Duplicates | Is the key really unique? Did a join multiply rows? | Aggregate before joining; COUNT(DISTINCT …) |
| Boundaries | Does the date filter include the last day? | Half-open ranges: >= start AND < next_start |
SELECT COUNT(*) AS customers,
COUNT(email) AS with_email,
COUNT(*) - COUNT(email) AS without_email
FROM customers;COUNT(*) counts rows; COUNT(email) counts only rows where email is not NULL. The difference is the number of customers with no email. Saying this out loud in an interview shows you know how aggregates treat NULL.
What different roles emphasise
The method is the same everywhere, but interviewers weigh things differently:
- Data analyst: correct business definitions (what counts as an active customer, revenue, churn), aggregation with GROUP BY and HAVING, joins without double counting, and explaining results in plain English.
- BI / analytics engineer: grain (one row per what?), star schemas, reusable and readable queries, window functions for period-over-period and running totals, consistent KPI definitions.
- Finance analyst: exact reconciliation (totals must tie out to the cent), signs of debits and credits, period boundaries, budget versus actual, and never silently dropping rows.
- Data engineer: deduplication, NULL handling, idempotent loads, keys and constraints, performance (indexes, EXPLAIN, avoiding row explosions) and data-quality checks.