25 SQL Interview Questions (with Practice Queries)

Create both tables and insert at least five customers, one of whom has no orders, and roughly ten orders spread across three months with at least one cancelled and one returned row. A small dataset you fully understand beats a realistic one you cannot verify by hand. When a query returns an unexpected number, you need to be able to count the correct answer manually.

For each question, say out loud what the output columns should be and roughly how many rows. A question like 'top 3 customers by revenue' should produce exactly 3 rows with two columns. This habit catches the join fan-out bug, where joining before aggregating multiplies your totals and you never notice because the query still runs.

SELECT customer_id, customer_name, signup_date FROM customers WHERE region = 'West' ORDER BY signup_date DESC; Use single quotes for strings, and never use double quotes, because some engines read double quotes as an identifier and the query will error. What the interviewer is listening for: that you can write a WHERE clause without reaching for a cheat sheet. A common mistake is writing region = West without quotes, which throws a column-not-found error in Postgres and silently fails in other engines.

SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'; The trap is using BETWEEN with two hardcoded dates. The interviewer wants to see relative date arithmetic, because in production nobody will tell you the dates. What they listen for: whether you know CURRENT_DATE, and whether you hint that the exact syntax varies by engine. Say out loud that you would check whether the warehouse uses DATEADD or INTERVAL before shipping it.

SELECT order_id, customer_id, status, total_amount FROM orders WHERE total_amount > 500 AND status <> 'cancelled' ORDER BY total_amount DESC; The interesting part is status <> 'cancelled'. That keeps rows where status is 'shipped', 'completed', and 'returned', which is usually not what the business means by a live order. Walk through that reasoning out loud and ask whether returned orders should also be excluded. This will break if status contains NULL, because NULL <> 'cancelled' evaluates to unknown and the row disappears.

SELECT region, COUNT(*) AS customer_count FROM customers GROUP BY region ORDER BY customer_count DESC; Alias the aggregate so the output reads well. What they listen for: whether you know that every non-aggregated column in the SELECT must appear in GROUP BY. Dropping region from the GROUP BY is the single most common error at this level, and the error message from the engine says so directly.

SELECT region, COUNT(*) AS customer_count FROM customers GROUP BY region HAVING COUNT(*) > 2000 ORDER BY customer_count DESC; The question exists to test WHERE versus HAVING. WHERE filters rows before grouping, HAVING filters groups after aggregation, and count-based conditions must use HAVING. What they listen for: that you can state that sentence, not just produce the query. Candidates who write WHERE COUNT(*) > 2000 have memorised the pattern without understanding it, and the follow-up question exposes that.

SELECT o.order_id, o.order_date, c.customer_name, c.region FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id; Alias your tables and prefix every column. It costs nothing and it saves you when a later join brings in a second customer_id. INNER JOIN returns only orders that have a matching customer, which is correct here because a foreign key should always match.

SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name ORDER BY order_count ASC; The critical detail is COUNT(o.order_id), not COUNT(*). COUNT(*) counts the unmatched row produced by the LEFT JOIN and returns 1 for a customer with no orders, which is wrong and is exactly the bug this question is designed to catch. What they listen for: whether you explain that choice unprompted. It is the fastest senior signal in the whole set.

SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL; The anti-join pattern. After a LEFT JOIN, unmatched rows have NULL in every column from the right table, so filtering on that NULL isolates them. What they listen for: that you know the predicate must go in WHERE and not in the ON clause, because moving it to ON undoes the outer join and turns the query into an inner join with zero rows.

SELECT c.customer_id, c.customer_name, COALESCE(SUM(o.total_amount), 0) AS revenue FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'completed' GROUP BY c.customer_id, c.customer_name ORDER BY revenue DESC; Two things matter here. The status filter belongs in the ON clause because a WHERE filter on the right table would remove the non-ordering customers again. And COALESCE turns the NULL total into 0. Note that SUM already returns NULL for no rows, while COUNT returns 0, so only the sum needs the wrapper.

SELECT o.order_id, o.customer_id, o.order_date, MAX(o2.order_date) AS previous_order_date FROM orders o LEFT JOIN orders o2 ON o2.customer_id = o.customer_id AND o2.order_date < o.order_date GROUP BY o.order_id, o.customer_id, o.order_date ORDER BY o.customer_id, o.order_date; A self join. The second alias o2 supplies the earlier order and MAX picks the most recent one among the matches. Explain that this can also be solved with LAG, and that on a large table the window function version will be faster because it avoids the join.

SELECT DATE_TRUNC('month', order_date) AS order_month, COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders, COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders FROM orders GROUP BY 1 ORDER BY 1; Conditional aggregation solves a huge share of real analyst questions. GROUP BY 1 is shorthand for the first select expression, which is fine in Postgres and looks careless in front of some interviewers, so I recommend writing the expression out in full during an interview.

SELECT c.customer_id, c.customer_name FROM customers c JOIN orders j ON c.customer_id = j.customer_id AND j.order_date >= '2026-01-01' AND j.order_date < '2026-02-01' JOIN orders f ON c.customer_id = f.customer_id AND f.order_date >= '2026-02-01' AND f.order_date < '2026-03-01'; Two joins to orders, one per month, and the intersection is implicit because INNER JOIN requires both to exist. Use half-open date ranges instead of dates between month ends, so you never miss a timestamp late on the last day. This is a very common real-world requirement and the interviewer is watching for exactly that date-boundary instinct.

SELECT c.region, ROUND(AVG(o.total_amount), 2) AS avg_order_value FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.status = 'completed' GROUP BY c.region ORDER BY avg_order_value DESC; AVG ignores NULLs, which is usually what you want but occasionally is not, because a region with many NULL amounts will still look healthy. What they listen for: whether you mention that AVG and COUNT treat NULL differently, and whether you ask whether returns should be netted off.

SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS lifetime_revenue FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.status = 'completed' GROUP BY c.customer_id, c.customer_name HAVING SUM(o.total_amount) > 5000 ORDER BY lifetime_revenue DESC; HAVING on a sum, with the row-level filter in WHERE. This combination is asked constantly. Write the WHERE first, then the GROUP BY, then the HAVING, and let the clauses stack in that order.

SELECT DATE_TRUNC('month', order_date) AS order_month, COUNT(DISTINCT customer_id) AS active_customers FROM orders WHERE status = 'completed' GROUP BY 1 ORDER BY 1; COUNT(DISTINCT) versus COUNT is a classic. A single customer with three orders contributes 3 to COUNT and 1 to COUNT(DISTINCT). On the 240,000-row orders table this is also the slowest query in the basic set, which makes it a natural bridge into the performance section.

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*), 2) AS cancelled_pct FROM orders; Two pitfalls. Multiplying by 100.0 rather than 100 forces decimal arithmetic, because integer division truncates and a real ratio of 4.7 percent becomes 4. And when the denominator comes from a filtered set, the denominator changes with it, so you get a percentage of the wrong base. Watch for that pattern in the answer you are shown.

SELECT COUNT(*) AS all_rows, COUNT(total_amount) AS non_null_amounts, COUNT(*) - COUNT(total_amount) AS null_amounts FROM orders; Say the rule plainly: aggregates except COUNT(*) skip NULLs, NULL compared to anything is unknown rather than false, so WHERE region = NULL returns nothing and you need WHERE region IS NULL. A common mistake is writing != 'cancelled' and assuming NULL rows are included. They are not, and silent row loss is the hardest bug to spot in a report.

WITH customer_revenue AS (SELECT c.customer_id, c.customer_name, c.region, SUM(o.total_amount) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.status = 'completed' GROUP BY c.customer_id, c.customer_name, c.region) SELECT customer_id, customer_name, region, revenue, RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS region_rank FROM customer_revenue ORDER BY region, region_rank; The CTE handles the aggregation and the window function ranks the result, which is cleaner than nesting a window inside an aggregate. Say out loud the difference between ROW_NUMBER, RANK, and DENSE_RANK: with a tie at the top, ROW_NUMBER gives 1 and 2, RANK gives 1 and 1 then 3, and DENSE_RANK gives 1 and 1 then 2.

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount, SUM(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders o WHERE o.status = 'completed' ORDER BY o.customer_id, o.order_date; The ROWS BETWEEN clause is explicit on purpose. Leaving it out gives the default frame, which is a range frame that includes ties, and two orders on the same date will both show the same running total. That surprise is a favourite follow-up question.

WITH ranked_orders AS (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC, o.order_id DESC) AS rn FROM orders o) SELECT order_id, customer_id, order_date, total_amount FROM ranked_orders WHERE rn = 1; The top-N-per-group pattern. Include order_id as a tiebreaker in the ORDER BY, otherwise two orders on the same date give a non-deterministic winner and the result changes between runs. Interviewers love asking why the tiebreaker is there, and the honest answer is reproducibility.

WITH monthly AS (SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(total_amount) AS revenue FROM orders WHERE status = 'completed' GROUP BY 1) SELECT order_month, revenue, LAG(revenue) OVER (ORDER BY order_month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY order_month)) / NULLIF(LAG(revenue) OVER (ORDER BY order_month), 0), 2) AS mom_pct FROM monthly ORDER BY order_month; The NULLIF in the denominator prevents a division-by-zero when a month has no revenue. Without it the query errors or returns a division error on the first real run. Mention the missing first month, where LAG returns NULL, and that you would usually filter it out for a chart.

SELECT o.order_id, o.customer_id, o.total_amount, ROUND(AVG(o.total_amount) OVER (PARTITION BY o.customer_id), 2) AS customer_avg, o.total_amount - AVG(o.total_amount) OVER (PARTITION BY o.customer_id) AS diff_from_avg FROM orders o WHERE o.status = 'completed' ORDER BY o.customer_id, o.order_date; A window function using an aggregate over a partition, which is the exact pattern the exam-style question is probing. The row count stays the same, unlike with GROUP BY, and that is the point you should state.

Take SELECT * FROM orders WHERE YEAR(order_date) = 2025. Wrapping the column in a function makes the index on order_date unusable, because the engine must evaluate YEAR() for every row. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01' so the predicate is sargable and the index can be seeked. On the 240,000-row orders table the difference is small; on a 240-million-row table it is the difference between two seconds and two minutes. What they listen for: whether you reach for the index rather than for more hardware.

Answer by query pattern, not by column. Foreign keys need one: create an index on orders(customer_id), because without it every join to customers scans the whole orders table. Date range filters want an index on orders(order_date). A composite index on orders(customer_id, order_date) serves both the join and the per-customer date ordering, and is usually better than two separate indexes. Say out loud that indexes cost write speed and storage, so you would not add them everywhere, and that you would confirm with EXPLAIN rather than assuming.

WITH dupes AS (SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, total_amount ORDER BY order_id) AS rn FROM orders) SELECT * FROM dupes WHERE rn > 1; Then delete the rows where rn > 1 after you have reviewed them. The trap is defining a duplicate on too few columns: two different orders from the same customer on the same day are not duplicates, and deleting them destroys revenue. I would always run the SELECT first, export the list, and get sign-off before any DELETE, because a delete on the wrong key is not reversible outside a restore.

Say three things in order: what you would check first and why, which table and column you would slice on, and what result would confirm or rule out each hypothesis. Candidates who start typing immediately usually answer the wrong question confidently. The interviewer is scoring the reasoning, and a clear structure with a smaller query beats a clever query with no explanation.

Close every answer with one limit: this excludes cancelled orders, this assumes customer_id is unique in customers, this will be slow on the full table without an index. That habit is what separates someone who has shipped reports from someone who has only done exercises. It also gives the interviewer an obvious thread to pull on, which is how interviews go well for you.