A structured SQL interview guide covering joins, aggregations, windows, subqueries, performance, data quality, and the questions behind 100 common problems.
The best way to prepare for SQL interviews is to understand the question behind the query. Interviewers want to see whether you can define the grain of a result, choose the right join, handle missing data, explain a trade-off, and validate the numbers. Use this guide as a map for 100 questions rather than a list to memorize.
Master the foundations first
The first group of questions checks SELECT, WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, CASE, NULL behavior, and date functions. These look simple but reveal whether you understand evaluation order and result grain. Before writing a query, say what one row in the output represents and which rows should be filtered before aggregation.
Practice with small tables on paper. Predict the output of a query with duplicates, nulls, and an empty group before running it. Then compare your prediction with the database. This habit catches logical errors faster than adding clauses until the result looks plausible.
Think clearly about joins
Joins are one of the highest-signal SQL topics. Know the difference between inner, left, right, and full joins, and understand that a one-to-many relationship can multiply rows. If a customer has three orders and you join another three-row table, the result may contain nine combinations unless you aggregate at the right stage.
Use explicit join conditions and qualify columns. For every join, ask whether unmatched rows should survive, whether the relationship is unique, and whether you need to deduplicate before or after joining. A correct join is a modeling decision, not only syntax.
Use windows for ranked and sequential analysis
Window functions solve problems that GROUP BY cannot solve cleanly because they calculate across related rows while keeping row detail. Practice ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, and moving averages. Always define the PARTITION BY and ORDER BY intentionally; a missing order can make a running result meaningless.
Common interview patterns include top two products per category, latest event per user, month-over-month change, consecutive activity, and running totals. Explain ties and nulls before coding. For large data, discuss how the window may sort or partition rows and what indexes or warehouse distribution could help.
Treat performance as part of correctness
A query that returns the right answer but cannot finish at scale is incomplete in a production interview. Learn to recognize functions on indexed columns, leading-wildcard searches, unnecessary DISTINCT, repeated correlated subqueries, accidental cross joins, and selecting unused columns. Use EXPLAIN conceptually even when the interview does not provide a real plan.
Performance improvements depend on the engine and workload. A B-tree index, columnar warehouse, partition, clustering key, or materialized view solves different problems. State the access pattern, data volume, freshness requirement, and write cost before recommending an optimization.
Validate data and communicate assumptions
SQL interviews often include ambiguous business language. Ask whether revenue means gross or net, whether dates use UTC, whether cancelled orders count, and how duplicate events should be treated. Write a query that makes those assumptions visible through named CTEs or comments.
Validate with reconciliation checks: total rows before and after joins, distinct entity counts, null rates, date boundaries, and a few hand-checked examples. Present the answer in layers—base data, cleaned data, metric calculation, and final output—so a reviewer can debug your thinking even if the first draft needs a correction.
A practical action plan
Turn this guide into a weekly workflow. Begin with the smallest action that creates evidence, then schedule a review before adding more complexity. Keep a short record of the decision you made, what happened, and what you learned. This record becomes useful in applications and interviews because it turns preparation into a story of ownership.
When you get stuck, separate a knowledge gap from a practice gap and a communication gap. A knowledge gap needs a focused explanation. A practice gap needs retrieval and repetition. A communication gap needs you to explain the same idea with a simpler structure. Naming the gap prevents random preparation and helps you spend time where it can change the outcome.
Quick reference table
| Area | What it demonstrates | Best preparation move |
|---|
| Questions 1–20: Foundations | Filtering, grouping, nulls, dates | State row grain and predict outputs with duplicates and NULL values. |
| Questions 21–45: Joins | Relationships and row multiplication | Draw cardinality and decide which unmatched rows survive. |
| Questions 46–75: Windows | Ranking and sequence logic | Practice partitions, tie handling, LAG/LEAD, and running totals. |
| Questions 76–100: Production SQL | Performance and judgment | Explain plans, indexes, partitions, quality checks, and assumptions. |
Before you apply or interview
- Practice 20 foundation questions.
- Draw table relationships before joins.
- Solve top-N and time-series patterns with windows.
- Read execution plans and discuss scale.
- Reconcile every result with a sanity check.
Finally, review the quality of your evidence from another person’s perspective. Can they understand the problem, your contribution, the result, and the next step without guessing? Clear evidence compounds: it improves your resume, your conversations, your interview answers, and your confidence at the same time.