Quick answer
Prepare for SQL interviews by practising real business questions rather than memorising syntax alone. Begin with filtering and grouping, then progress to joins, window functions, dates and data-quality checks. For every solution, define the expected grain, test duplicate behaviour and explain what the query means for the business.
A candidate may remember the syntax for ROW_NUMBER and still produce an incorrect answer if a join duplicates transactions. SQL interviews test more than code: they often test metric definitions, assumptions, data quality and interpretation. This guide uses a fictional commerce dataset so the exercises are concrete without exposing private customer records. Syntax differs across SQL dialects, so treat snippets as conceptual patterns and confirm functions against the platform used in your assessment.
Start by defining table grain
The grain is what one row represents: one order, one order item, one customer or one event. Mixing grains is a frequent source of doubled counts. Before coding, write down each table’s primary key and whether relationships are one-to-one or one-to-many. If you cannot describe the grain, clarify it in an interview rather than guessing. Treat row counts after joins as diagnostic information, not merely a query output.
Practical example: In a fictional schema, customers has one row per customer and orders has one row per order. Joining them increases rows for repeat buyers; counting joined rows does not count unique customers.
Translate business questions into metrics
“How many active customers did we have?” is ambiguous until you define active, customer, date range and timezone. Ask whether cancelled orders count and whether activity means purchase, login or subscription. A strong analyst frames the metric before typing SELECT. This is especially important when the interviewer expects business judgement rather than a single fixed answer. Write the requested output columns and their grain before picking an aggregation.
Practical example: For monthly orders, define month using the order creation timestamp and decide whether to count orders placed, shipped or completed. State this assumption before giving totals.
Practise WHERE, GROUP BY and HAVING deliberately
WHERE filters rows before aggregation, while HAVING filters groups after aggregation. A basic sales question can reveal whether you understand the difference. Be careful when you include non-grouped columns in SELECT; permissive SQL settings can hide mistakes. Use COUNT(DISTINCT …) only when it matches the intended entity, not as a blanket cure for incorrect joins. Compare your result with an independently computed small sample.
Practical example: Ask: “Which product categories had more than ten completed orders last month?” First filter completed orders and date range, group by category, then apply the group count threshold.
Know the effect of each join type
INNER JOIN removes unmatched rows, while LEFT JOIN preserves rows from the left side and fills missing right-side fields with NULL. Interviewers may probe why results disappear or inflate. Ask whether unmatched customers should remain in the output. When joining two detail tables, avoid many-to-many multiplication unless it is intentional. Draw a tiny table on paper with two customers and three transactions to check the expected result before running the query.
Practical example: To find customers without orders, LEFT JOIN customers to orders and filter where a joined order key is NULL, or use NOT EXISTS. Explain how each handles unmatched records.
Understand NULL and three-valued logic
NULL represents missing or unknown information, and comparisons with NULL behave differently from ordinary equality checks. Use IS NULL rather than = NULL. Be deliberate when calculating averages, because AVG commonly ignores NULL values, which may or may not match the business requirement. Distinguish truly missing data from a genuine zero. A correct query should communicate what missing values mean, not merely return a number.
Practical example: If a survey score is absent, treating it as zero changes the average. Tell the interviewer whether missing responses should be excluded or separately counted.
Use window functions without losing rows
Window functions can rank within groups and compute running totals while retaining detail rows. Compare ROW_NUMBER, RANK and DENSE_RANK on a tied example. Define tie-breaking order when you need exactly one latest event per customer; otherwise results can be unstable. Partitioning by the wrong entity is a common interview error. The useful explanation is not the function name but what the returned row represents.
Practical example: Find the latest order per customer using ROW_NUMBER partitioned by customer_id and ordered by order_date descending plus a stable order_id tie-breaker. Filter row_number = 1 in an outer query.
Practise date boundaries and cohorts
Dates cause subtle problems when a timestamp includes timezones or when “last month” includes partial days. Define whether a range is inclusive and choose boundaries that avoid double-counting. For retention questions, decide whether users belong to a signup cohort and what qualifies as returning activity. Distinguish calendar months from fixed 30-day intervals. Different SQL engines provide different date functions, so focus on the reasoning first.
Practical example: Define a February cohort as users whose signup timestamp falls within the calendar month in a stated timezone. Count those users before measuring who returns in March.
Explain query performance at the right level
Efficiency matters when tables are large, but avoid claiming exact runtime without execution evidence. Discuss filtering early when semantically safe, suitable join keys, index availability and avoiding unnecessary repeated scans. Performance depends on engine, partitions, data distribution and optimizer behaviour. When asked for an optimisation, state a hypothesis and propose an EXPLAIN plan or benchmark. Correctness should remain the baseline; a faster incorrect metric is not a success.
Practical example: If a query scans all events for one month, ask whether date partitions exist and whether the predicate can benefit from partition pruning. Do not promise a specific percentage improvement.
Learn to detect data-quality problems
An interviewer may give contradictory records, duplicate keys or negative transaction values. Instead of silently dropping them, show checks that quantify the issue. Ask which system is authoritative and whether refunds should subtract from sales. Count records before and after each filter. A simple exception report can demonstrate stronger analysis than a complicated final chart that hides the problem.
Practical example: Write a check for duplicate order IDs and a separate check for order totals below zero. Then explain whether either can be legitimate and who should confirm.
Practise talking through a SQL solution
A good explanation follows problem definition, table grain, join path, calculation, validation and limitations. Practise doing this aloud in a few minutes. If an interviewer changes the requirement—for example from “orders” to “paying customers”—pause and update the grain and denominator. Keep a notebook of mistakes you repeatedly make and revisit them with small counterexamples. A completed query without explanation may pass an automated judge but underperform in a collaborative interview.
Practical example: After writing a query, describe one edge case and one data-quality check. Ask yourself what business decision someone could safely make from the result and what uncertainty remains.
Worked example
Fictional case: a retailer asks for repeat-purchase rate. The orders table has 200 rows for 130 customers, but several orders are cancelled and some customers share the same email address. Before computing anything, define whether repeat means at least two completed orders and whether customer_id is the stable identifier. Filter to completed orders, group by customer_id, count customers with two or more qualifying orders and divide by all customers with at least one qualifying order. Check the zero-denominator case and explain that the result describes this observed period, not lifetime customer loyalty. This reasoning is more valuable than a memorised one-line query.
Put this into practice
Use a seven-session plan. Session 1: grain and filtering. Session 2: grouping and metric definitions. Session 3: joins and unmatched rows. Session 4: NULLs and edge cases. Session 5: windows and latest-record logic. Session 6: dates, cohorts and retention. Session 7: a timed end-to-end business case with an explanation. After every session, save one failing counterexample and a corrected query. Revisit the same mistake later rather than endlessly collecting new questions. Keep dialect-specific documentation open only when the practice rules permit it.
Frequently asked questions
Which SQL dialect is best for interviews?
Use the dialect expected by the employer when known; otherwise clearly state your chosen syntax and note differences.
Should I memorise window functions?
Understand partitioning, ordering and tie behaviour. Memorised syntax alone will not prevent incorrect results.
How do I avoid duplicate counts?
Define the grain, inspect join cardinality, compare row counts and use DISTINCT only when the metric requires it.
Can I use CTEs in interviews?
Often yes, when supported. Clear common table expressions can make assumptions and intermediate calculations easier to review.
How many SQL problems should I solve?
Focus on representative patterns and error review rather than an arbitrary count. Practise explaining both correctness and business meaning.
Final takeaway
Excellent SQL preparation combines syntax, data modelling, metric definitions and quality checks. Practise with small counterexamples and describe your assumptions explicitly so your solution remains defensible when requirements change.