SQL Interview Preparation: Patterns, Reasoning, and Practice

Effective SQL interview preparation is less about memorizing syntax and more about turning an ambiguous business question into a precise query. Practice recurring patterns—joins, aggregation, deduplication, dates, ranking, cohorts, and sequential analysis—while stating the output grain, join cardinality, NULL behavior, and tie policy before coding. Then test each query, inspect at least some execution plans, and explain why the result answers the question.

Key takeaways

• State the output grain before writing SQL: identify exactly what one returned row represents.
• Treat joins, aggregation, deduplication, cohort metrics, ranking, and window functions as separate practice patterns.
• Separate correctness from performance: first validate rows and business meaning, then use EXPLAIN to investigate the plan.
• In a live interview, narrate assumptions about duplicates, missing records, NULLs, ties, date boundaries, and metric definitions.
• Use a seven-day practice cycle that includes untimed reasoning, timed mixed questions, debugging, and a simulated interview.

SQL interview preparation is the deliberate practice of translating data questions into correct, explainable, and testable SQL. The core skill is not merely knowing SELECT, JOIN, or GROUP BY; it is controlling the relationship between the business question, the data model, and the rows your query returns. A strong candidate can say what the result means, why each table is needed, how duplicates are handled, and how the answer would be checked.

Start with grain, joins, and metric meaning

Before touching the keyboard, define the output grain. Say, for example, “I need one row per customer per calendar month” or “I need one row per order.” This single sentence constrains the rest of the query. If you cannot state the grain, you are not yet ready to choose the joins or aggregation level.

Next, inspect the cardinality of every relationship. An orders table may have many rows per customer, and an order_items table may have many rows per order. Joining both to customers can multiply rows before aggregation. That multiplication may be intended when calculating item-level revenue, but it can silently inflate an order-level count. Ask whether the measure should be counted before or after the one-to-many join.

A useful interview script is: “The output is one row per customer. I will join orders on customer_id, preserve customers with no orders using a LEFT JOIN if they belong in the population, and aggregate order values at the customer level. I will check whether order_id is unique before counting.” This demonstrates data reasoning before syntax.

Reporting and metrics questions often combine joins, subqueries, window functions, and ambiguous metric definitions, so they deserve more attention than isolated syntax drills. Interview Query groups these questions as a major SQL interview category. When a prompt says “active users,” ask what active means: a login, a completed event, a paid transaction, or any recorded activity?

Practice these foundational patterns deliberately:

• Multi-table joins: combine users, orders, events, or products without creating unintended duplicates.
• Aggregation and HAVING: calculate totals or averages at the requested grain, then filter groups rather than individual rows.
• Deduplication: use ROW_NUMBER or a well-defined aggregate to select one record, and explain which record wins.
• Missing-row analysis: use LEFT JOIN and a NULL check to find customers with no events, failed payments, or inactive accounts.
• NULL behavior: distinguish a missing value from zero, and know how COUNT(column) differs conceptually from counting rows.

Build a pattern-based SQL practice set

Once fundamentals are stable, organize questions by archetype rather than by random difficulty. This helps you recognize the structure of a new prompt. The table name may change, but the reasoning pattern often remains familiar.

For date-based reporting, clarify the boundary first. “Last month” might mean the previous calendar month, the last 30 rolling days, or a reporting period in a particular time zone. Write the boundary explicitly and consider whether timestamps need conversion before comparison. For cohorts and retention, define the cohort event, the return event, and the denominator. A retention percentage without a stated denominator is not a complete metric.

For ranking and top-N questions, decide whether ranking is global or within a group. “Top three products per category” calls for a partitioned ranking, not one global LIMIT 3. Also state how ties behave. A ROW_NUMBER result returns a fixed number of rows, while a tie-preserving approach may return more than N rows. The interviewer is often testing whether you notice that distinction.

Window functions deserve their own practice block because they calculate across related rows without collapsing the individual rows. PostgreSQL documents ranking, distribution, offset, and aggregate window functions, and emphasizes the role of partitioning, ordering, and frame behavior in the result. Review the PostgreSQL window-function documentation while practicing `ROW_NUMBER`, `RANK`, `LAG`, `LEAD`, running totals, and moving averages.

A realistic sequential-analysis prompt might ask for each customer’s first purchase and the number of days until the next purchase. One approach is to order purchases per customer and use `LEAD(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date)`. Before writing it, state what happens when two purchases share a timestamp and what the final purchase should show for its missing next date.

Use CTEs when they make the reasoning visible. A good sequence might be `customer_orders`, then `first_order`, then `monthly_summary`. CTEs are not automatically better for every execution plan, but they can make an interview answer easier to inspect and explain. The goal is not to create a long query; it is to expose the transformations in a logical order.

Use a repeatable reasoning and testing loop

For each problem, separate design from execution. Spend roughly 15–20 minutes designing the query before running it: identify the grain, list the required tables, predict row counts after each join, define filters, and write down edge cases. This workflow is consistent with Interview Query’s recommended approach to attempting, debugging, and comparing SQL solutions.

1. Restate the business question and define the returned entity or grouping.
2. List the source tables and the join key for each relationship.
3. Predict whether each join is one-to-one, one-to-many, or potentially many-to-many.
4. Choose filters and date boundaries, including whether missing rows must remain.
5. Choose the aggregation level and define how NULLs, duplicates, and ties behave.
6. Write the query in small, inspectable steps such as CTEs or subqueries.
7. Test with a tiny mental dataset containing a duplicate, a NULL, a tie, and a missing record.
8. Explain the result in plain language before optimizing it.

Create tiny test cases instead of trusting a query that merely runs. Suppose the task is to find the second purchase per customer. Test a customer with one purchase, one with two purchases on the same date, and one with three purchases. If your ordering is not deterministic, the second row may not mean what you think it means. If the prompt asks for customers with no purchases, include a customer with no matching order and verify that the LEFT JOIN preserves that customer.

Keep a bug log. Record mistakes such as filtering a LEFT JOINed table in the WHERE clause and accidentally removing unmatched rows, aggregating after a duplicating join, using the wrong date boundary, or applying a window function at the wrong grain. Revisit the log before a mock interview; repeated mistakes are more valuable preparation targets than unfamiliar syntax.

Add performance reasoning without memorizing trivia

Correctness comes first, but interviewers may ask how the query would behave at scale. Do not respond by listing index types without connecting them to the query. Start by identifying the expensive operation: a broad scan, a large join, a sort for ranking, or an aggregation over too many rows. Then explain what evidence you would inspect.

PostgreSQL describes an execution plan as a tree of scans, joins, aggregations, and sorts. `EXPLAIN ANALYZE` adds actual row counts and runtime, allowing you to compare estimates with what happened during execution. The PostgreSQL EXPLAIN guide supports a practical habit: after an advanced practice query, read the plan once and identify where estimated and actual work diverge.

A concise performance explanation might be: “I would first confirm the query is correct and check the plan. If the join produces far more rows than expected, I would verify the join key and pre-aggregate the many-side data if the business logic permits. If a sort dominates the work, I would check whether the ranking requirement can be narrowed by filtering earlier, without changing the intended result.” This shows judgment rather than index memorization.

Do not claim that a rewrite is faster merely because it is shorter. Compare plans and actual behavior when possible. Also distinguish a query that is slow because it processes the required data from one that is slow because it creates accidental intermediate rows. That distinction is especially important in reporting questions.

Follow a seven-day preparation plan

A short plan works best when every day produces evidence of improvement: solved questions, recorded explanations, and a list of recurring errors. Keep the sessions focused rather than trying to cover every SQL feature.

1. Days 1–2: Practice joins, filtering, aggregation, HAVING, NULL behavior, and one-to-many cardinality. For each query, state the output grain aloud.
2. Days 3–4: Practice CTEs, deduplication, date boundaries, cohort definitions, and window functions including ROW_NUMBER, RANK, LAG, and LEAD.
3. Day 5: Work on metric-definition questions. Before coding, define terms such as active user, conversion, retention, revenue, and first purchase.
4. Day 6: Complete a timed mixed set. Do not immediately look up an answer; record the assumption or bug that blocked you.
5. Day 7: Run a simulated interview with a written prompt, a time limit, verbal explanation, and follow-up questions about correctness and performance.

At the end of every session, score each solution against the same rubric:

• Grain: Does every returned row represent the intended entity?
• Joins: Are keys and cardinalities correct, with no accidental multiplication?
• Filters: Are date boundaries and population rules explicit?
• Missing data: Are NULLs and unmatched rows handled intentionally?
• Ties and duplicates: Is the selection policy stated and deterministic where needed?
• Readability: Can another person follow the query’s stages?
• Communication: Can you explain why the result answers the business question?
• Validation: Did you test edge cases and, where relevant, inspect the plan?

During the interview, ask clarifying questions that improve correctness rather than delay the work: “Should customers with no activity appear?” “Is the date range calendar-based or rolling?” “Should ties at the cutoff all be included?” “Can one order contain multiple items?” If the interviewer cannot specify, state a reasonable assumption and continue. A visible assumption is easier to evaluate and revise than a silent one.

SQL interview preparation is strongest when every query is treated as a small reasoning exercise. Recognize the archetype, define the grain, map the joins, specify edge-case behavior, write in readable stages, test against counterexamples, and explain the business meaning. Add plan reading after correctness, not instead of it. That routine prepares you for unfamiliar schemas because it gives you a method for discovering what the query must do before deciding how to write it.