Chapter 6 · Practice and Capstone
SQL Interview and Problem-Solving Practice
- Page 21 of 22
- 19 min read
SQL interviews and online assessments for data, ML and AI engineering roles come back to the same dozen patterns: filtering with NULLs, aggregating without double counting, finding what is missing, ranking within groups, comparing a row with the previous one, and spotting why a query gives the wrong number. Once you recognise the pattern, the query almost writes itself. This page is a workout: graded problems on the shop data, each with a solution and, more important, the reasoning an interviewer wants to hear.
Try each problem yourself before reading the solution. Every query runs on SQLite (3.25+ for window functions); notes point out where MySQL or PostgreSQL differ.
What you will learn
- A repeatable routine for any SQL problem, built on the logical execution order
- The classic patterns: NULL-safe filters, anti-joins, pre-aggregation against fan-out, correlated subqueries, Nth highest, top-N per group
- Window-function patterns: running totals, month-over-month growth, gaps-and-islands, retention, deduplication
- How to debug a wrong query and how to make a slow one sargable
- How to manage a timed assessment
A routine for every problem
- Restate the question and pin down the grain of the answer: "one row per category", "one row per customer per month".
- Ask about edge cases: cancelled orders? ties? NULLs? categories with no sales? If you cannot ask, state your assumption in a comment.
- Build in steps: run the
FROM/JOINpart first and check the row count, then add filters, then grouping. CTEs make each step readable. - Check the result against something you know: a total, a count, one row computed by hand.
And keep the logical order in mind; it explains most "why can't I use that alias here?" errors:
FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT (window functions here) → DISTINCT → ORDER BY → LIMITWindow functions run after WHERE and GROUP BY, so you cannot filter on them directly — wrap them in a CTE or subquery first. That one fact answers several problems below.
Level 1: filtering and NULLs
Problem 1. Customers outside Dhaka
List customers who are not in Dhaka. The obvious query is wrong:
SELECT customer_id, name, city FROM customers WHERE city <> 'Dhaka' ORDER BY customer_id;
SELECT customer_id, name, city
FROM customers
WHERE city <> 'Dhaka' OR city IS NULL
ORDER BY customer_id;+-------------+---------------+------------+
| customer_id | name | city |
+-------------+---------------+------------+
| 2 | Tanvir Ahmed | Chattogram |
| 4 | Rafiq Islam | Sylhet |
| 6 | Imran Hossain | Khulna |
| 8 | Karim Uddin | Rajshahi |
+-------------+---------------+------------+
+-------------+-----------------+------------+
| customer_id | name | city |
+-------------+-----------------+------------+
| 2 | Tanvir Ahmed | Chattogram |
| 4 | Rafiq Islam | Sylhet |
| 5 | Sadia Chowdhury | NULL |
| 6 | Imran Hossain | Khulna |
| 8 | Karim Uddin | Rajshahi |
+-------------+-----------------+------------+Reasoning: NULL <> 'Dhaka' is unknown, not true, so Sadia (city unknown) vanishes from the first query. Whether she belongs in the answer is a business question — say so. SQLite and PostgreSQL also offer city IS NOT 'Dhaka' / city IS DISTINCT FROM 'Dhaka'.
Problem 2. Products you can sell right now
Products in stock costing under 3,000 taka, cheapest first.
SELECT product_id, name, price, stock
FROM products
WHERE price < 3000
AND (stock > 0 OR stock IS NULL) -- NULL stock = digital product, never runs out
ORDER BY price, product_id;+------------+---------------------------+-------+-------+
| product_id | name | price | stock |
+------------+---------------------------+-------+-------+
| 4 | Wireless Mouse | 900 | 60 |
| 11 | Gift Card | 1000 | NULL |
| 1 | Python Crash Course | 1200 | 40 |
| 8 | Laptop Stand | 1500 | 25 |
| 2 | Hands-On Machine Learning | 2500 | 15 |
+------------+---------------------------+-------+-------+Reasoning: the USB-C Hub (stock 0) is out. Courses and the gift card have NULL stock because they are digital; stock > 0 alone would silently drop them. The tie-breaker product_id makes the order fully determined.
Level 2: aggregation and joins
Problem 3. Revenue per category, including empty ones
Revenue per category from non-cancelled orders. Show every category, even with no sales.
SELECT c.name AS category,
COALESCE(SUM(CASE WHEN o.status <> 'cancelled'
THEN oi.quantity * oi.unit_price END), 0) AS revenue
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.category_id
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
LEFT JOIN orders AS o ON o.order_id = oi.order_id
GROUP BY c.category_id, c.name
ORDER BY revenue DESC, category;+-------------+---------+
| category | revenue |
+-------------+---------+
| Courses | 36000 |
| Electronics | 31550 |
| Accessories | 8900 |
| Books | 8600 |
| Furniture | 0 |
+-------------+---------+Reasoning: start from categories so Furniture survives; COALESCE turns its NULL sum into 0. The cancelled filter sits inside the SUM, not in WHERE: a WHERE o.status <> 'cancelled' would drop the NULL rows of empty categories and turn the LEFT JOIN back into an inner join, and even WHERE o.status IS NULL OR … would still lose a category whose only sales were cancelled. Conditional aggregation keeps every category and simply adds nothing for cancelled lines. Electronics excludes the cancelled monitor (order 5). The Gift Card has no category, so it is in no row — mention it.
Problem 4. Customers who never ordered
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;+-------------+-------------+
| customer_id | name |
+-------------+-------------+
| 8 | Karim Uddin |
+-------------+-------------+Reasoning: this is an anti-join. LEFT JOIN … WHERE o.order_id IS NULL works too. Avoid NOT IN (SELECT customer_id …): if that subquery ever returns a NULL, the result is empty.
Problem 5. Orders whose payments do not match
Find orders where the amount paid differs from the order total. The tempting query joins items and payments together:
-- Wrong: joining two "many" tables to orders multiplies rows
SELECT o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total,
COALESCE(SUM(p.amount), 0) AS paid
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
LEFT JOIN payments AS p ON p.order_id = o.order_id
GROUP BY o.order_id
HAVING order_total <> paid
ORDER BY o.order_id;+----------+-------------+-------+
| order_id | order_total | paid |
+----------+-------------+-------+
| 1 | 2100 | 4200 |
| 4 | 4000 | 8000 |
| 5 | 18500 | 0 |
| 6 | 30000 | 15000 |
| 7 | 4000 | 8000 |
| 9 | 4950 | 9900 |
| 11 | 5500 | 11000 |
| 13 | 15000 | 0 |
| 14 | 3100 | 6200 |
+----------+-------------+-------+Nine "mismatches" — almost all fake. An order with two items and one payment counts the payment twice; order 6 (one item, two payments) counts the item twice. Fix: aggregate each side to one row per order first, then join:
WITH totals AS (
SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items GROUP BY order_id
),
paid AS (
SELECT order_id, SUM(amount) AS paid
FROM payments GROUP BY order_id
)
SELECT o.order_id, o.status, t.order_total, COALESCE(p.paid, 0) AS paid
FROM orders AS o
JOIN totals AS t ON t.order_id = o.order_id
LEFT JOIN paid AS p ON p.order_id = o.order_id
WHERE t.order_total <> COALESCE(p.paid, 0)
ORDER BY o.order_id;+----------+-----------+-------------+------+
| order_id | status | order_total | paid |
+----------+-----------+-------------+------+
| 5 | cancelled | 18500 | 0 |
| 13 | pending | 15000 | 0 |
+----------+-----------+-------------+------+Reasoning: only the cancelled order and the pending one are unpaid; everything else reconciles, including order 6's split payment. "Pre-aggregate before joining" is the single most useful sentence in SQL interviews.
Level 3: subqueries and ranking
Problem 6. Products priced above their category's average
SELECT p.name, p.category_id, p.price
FROM products AS p
WHERE p.price > (SELECT AVG(p2.price) FROM products AS p2
WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC;+---------------------------+-------------+-------+
| name | category_id | price |
+---------------------------+-------------+-------+
| Hands-On Machine Learning | 1 | 2500 |
| 27-inch Monitor | 2 | 18500 |
| AI Engineering Bootcamp | 3 | 15000 |
| USB-C Hub | 4 | 2200 |
+---------------------------+-------------+-------+Reasoning: a correlated subquery runs once per outer row with that row's category. The same with a window: compute AVG(price) OVER (PARTITION BY category_id) in a CTE, then filter outside it. The gift card (NULL category) is compared with an average of nothing (NULL), so it never appears.
Problem 7. The second-highest salary
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
WITH ranked AS (
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2
ORDER BY department, name;+----------------+
| second_highest |
+----------------+
| 180000 |
+----------------+
+-------------+--------------+--------+
| department | name | salary |
+-------------+--------------+--------+
| Data | Priya Sen | 110000 |
| Engineering | Nusrat Jahan | 120000 |
| Engineering | Zahid Hasan | 120000 |
+-------------+--------------+--------+Reasoning: the first form returns NULL (not an empty result) when there is no second value — interviewers like that. For "Nth highest per group", DENSE_RANK handles ties: Nusrat and Zahid both earn 120,000, so both are second in Engineering. ROW_NUMBER would pick one arbitrarily, RANK would skip the number after a tie.
Problem 8. Top 2 products by revenue in each category
WITH product_revenue AS (
SELECT p.category_id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.product_id
JOIN orders AS o ON o.order_id = oi.order_id AND o.status <> 'cancelled'
GROUP BY p.product_id, p.category_id, p.name
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC, name) AS rn
FROM product_revenue
)
SELECT category_id, name, revenue, rn
FROM ranked
WHERE rn <= 2
ORDER BY category_id, rn;+-------------+---------------------------+---------+----+
| category_id | name | revenue | rn |
+-------------+---------------------------+---------+----+
| 1 | Hands-On Machine Learning | 5000 | 1 |
| 1 | Python Crash Course | 3600 | 2 |
| 2 | 27-inch Monitor | 18500 | 1 |
| 2 | Mechanical Keyboard | 8550 | 2 |
| 3 | AI Engineering Bootcamp | 30000 | 1 |
| 3 | SQL Masterclass | 6000 | 2 |
| 4 | Laptop Stand | 4500 | 1 |
| 4 | USB-C Hub | 4400 | 2 |
+-------------+---------------------------+---------+----+Reasoning: aggregate first, rank second, filter third — the filter must live outside the CTE because window functions run after WHERE. Adding name to the window's ORDER BY makes ties deterministic.
Level 4: time-series patterns
Problem 9. Monthly revenue, running total and month-over-month growth
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month;+---------+---------+---------------+---------+
| month | revenue | running_total | mom_pct |
+---------+---------+---------------+---------+
| 2026-01 | 6600 | 6600 | NULL |
| 2026-02 | 7000 | 13600 | 6.1 |
| 2026-03 | 21400 | 35000 | 205.7 |
| 2026-04 | 23450 | 58450 | 9.6 |
| 2026-05 | 8500 | 66950 | -63.8 |
| 2026-06 | 18100 | 85050 | 112.9 |
+---------+---------+---------------+---------+Reasoning: 100.0 forces decimal division (integer division would truncate). January's growth is NULL because there is no previous month — correct, do not turn it into 0. In a real interview mention missing months: a month with no orders produces no row, and LAG would then compare with two months ago. Join to a calendar (a recursive CTE) to fix it, and guard the division with NULLIF(…, 0) in case a month had zero revenue — both as in Analytics Patterns: Growth, Cohorts, Retention and Gaps.
Problem 10. Longest login streak (gaps and islands)
For each user, find the longest run of consecutive login days.
CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1, '2026-06-01'), (1, '2026-06-02'), (1, '2026-06-03'), (1, '2026-06-05'), (1, '2026-06-06'),
(2, '2026-06-01'), (2, '2026-06-03'), (2, '2026-06-04'), (2, '2026-06-05'), (2, '2026-06-06'),
(2, '2026-06-06'), (3, '2026-06-02');
WITH days AS (
SELECT DISTINCT user_id, login_date FROM logins
),
islands AS (
SELECT user_id, login_date,
julianday(login_date)
- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
FROM days
),
streaks AS (
SELECT user_id, MIN(login_date) AS start_day, MAX(login_date) AS end_day, COUNT(*) AS length
FROM islands
GROUP BY user_id, grp
)
SELECT user_id, start_day, end_day, length
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY length DESC, start_day) AS rn
FROM streaks)
WHERE rn = 1
ORDER BY user_id;+---------+------------+------------+--------+
| user_id | start_day | end_day | length |
+---------+------------+------------+--------+
| 1 | 2026-06-01 | 2026-06-03 | 3 |
| 2 | 2026-06-03 | 2026-06-06 | 4 |
| 3 | 2026-06-02 | 2026-06-02 | 1 |
+---------+------------+------------+--------+Reasoning: within a run of consecutive days, the date and the row number both go up by one, so their difference is constant; each constant is one island. Remove duplicates first (user 2 logged in twice on 6 June), or the row numbers drift. The same trick finds outages, streaks of failed LLM calls and sessions.
Problem 11. Repeat-purchase rate by first-order month (retention)
WITH valid AS (
SELECT customer_id, order_date FROM orders WHERE status <> 'cancelled'
),
firsts AS (
SELECT customer_id, MIN(order_date) AS first_date FROM valid GROUP BY customer_id
)
SELECT strftime('%Y-%m', f.first_date) AS cohort,
COUNT(*) AS customers,
SUM(EXISTS (SELECT 1 FROM valid AS v
WHERE v.customer_id = f.customer_id
AND strftime('%Y-%m', v.order_date) > strftime('%Y-%m', f.first_date))) AS came_back,
ROUND(100.0 * SUM(EXISTS (SELECT 1 FROM valid AS v
WHERE v.customer_id = f.customer_id
AND strftime('%Y-%m', v.order_date) > strftime('%Y-%m', f.first_date))) / COUNT(*), 0) AS pct
FROM firsts AS f
GROUP BY cohort
ORDER BY cohort;+---------+-----------+-----------+-------+
| cohort | customers | came_back | pct |
+---------+-----------+-----------+-------+
| 2026-01 | 2 | 2 | 100.0 |
| 2026-02 | 1 | 1 | 100.0 |
| 2026-03 | 1 | 0 | 0.0 |
| 2026-04 | 1 | 1 | 100.0 |
| 2026-05 | 1 | 0 | 0.0 |
+---------+-----------+-----------+-------+Reasoning: a cohort is defined by the first valid order (Rafiq's only order was cancelled, so he is in no cohort). "Came back" means an order in a later month. In SQLite EXISTS returns 0/1 so it can be summed; in PostgreSQL write COUNT(*) FILTER (WHERE …) or SUM(CASE …). A full retention table adds one column per "months since first order" (see Analytics Patterns: Growth, Cohorts, Retention and Gaps).
Level 5: cleaning, debugging and speed
Problem 12. Duplicates that differ only in case
INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES
(9, 'Nadia R.', ' NADIA@example.com', 'Dhaka', '2026-06-20');
SELECT LOWER(TRIM(email)) AS clean_email, COUNT(*) AS n, GROUP_CONCAT(customer_id ORDER BY customer_id) AS ids
FROM customers
GROUP BY clean_email
HAVING COUNT(*) > 1;
WITH ranked AS (
SELECT customer_id,
ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY joined_on, customer_id) AS rn
FROM customers
)
SELECT customer_id AS duplicate_to_remove FROM ranked WHERE rn > 1;+-------------------+---+-----+
| clean_email | n | ids |
+-------------------+---+-----+
| nadia@example.com | 2 | 1,9 |
+-------------------+---+-----+
+---------------------+
| duplicate_to_remove |
+---------------------+
| 9 |
+---------------------+Reasoning: the UNIQUE constraint did not stop it, because ' NADIA@example.com' is a different string. (ORDER BY inside GROUP_CONCAT needs SQLite 3.44+; PostgreSQL writes STRING_AGG(customer_id::text, ',' ORDER BY customer_id).) Normalise, group, then keep one row per group with ROW_NUMBER — oldest first. In a real system you would also move the duplicate's orders to the kept customer before deleting, and add a unique index on LOWER(TRIM(email)).
Problem 13. Debug: "customers with zero orders show 1 order"
A colleague wants each customer's number of delivered orders, including zeros:
SELECT c.customer_id, COUNT(*) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY c.customer_id;+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
+-------------+------------------+Two bugs. The WHERE on the right table removes customers with no delivered orders (4, 8, 9 are gone), turning the LEFT JOIN into an inner join. And if they were kept, COUNT(*) would count their single NULL-filled row as 1. Move the condition into ON and count a column from the right table:
SELECT c.customer_id, COUNT(o.order_id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY c.customer_id;+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 4 | 0 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 0 |
| 9 | 0 |
+-------------+------------------+Problem 14. Make it fast: sargable filters
"This query is slow on 50 million orders. Why?"
CREATE INDEX idx_orders_date ON orders (order_date);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE strftime('%Y-%m', order_date) = '2026-03';
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';+----+--------+---------+--------------------------------------------------+
| id | parent | notused | detail |
+----+--------+---------+--------------------------------------------------+
| 3 | 0 | 0 | SCAN orders USING COVERING INDEX idx_orders_date |
+----+--------+---------+--------------------------------------------------+
+----+--------+---------+------------------------------------------------------------------------------------+
| id | parent | notused | detail |
+----+--------+---------+------------------------------------------------------------------------------------+
| 3 | 0 | 0 | SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?) |
+----+--------+---------+------------------------------------------------------------------------------------+Reasoning: wrapping the column in a function hides its order from the index, so SQLite cannot jump to March: it reads every entry (SCAN — here through the index, which is smaller than the table, but still all of it). A half-open range on the bare column is sargable: the plan becomes SEARCH and reads only March. Other answers interviewers expect: select only needed columns, avoid leading-% LIKE, filter before joining, check the plan with EXPLAIN (see EXPLAIN and Query Optimization).
Timed assessments
- Read every question first and start with the ones you can finish; partial credit is often per question.
- Look at the data before writing:
SELECT * … LIMIT 5on each table, and check for NULLs and duplicates in key columns. - Write in CTE steps and run each one. A correct, readable answer beats a clever one-liner you cannot debug.
- Check the dialect.
LIMITvsTOP,strftimevsDATE_TRUNC/DATE_FORMAT,||vsCONCAT. Many platforms run MySQL or PostgreSQL. - Leave two minutes per question to sanity-check: does the row count match the grain? Do totals match a simple
SUM? - Talk (in live interviews): say the assumption, the pattern and the trade-off. The reasoning is graded as much as the query.
Common mistakes
- Forgetting NULLs in filters.
<>,NOT INand comparisons silently drop NULL rows. Fix: decide explicitly, withIS NULL,COALESCEorIS DISTINCT FROM. - Joining two one-to-many tables at once. Totals multiply. Fix: aggregate each side per key in a CTE, then join.
- Filtering a LEFT JOIN's right table in
WHERE. It becomes an inner join. Fix: put the condition inON. - Using a window function in
WHERE. It is not computed yet. Fix: compute it in a CTE and filter outside. - Non-deterministic ordering.
ORDER BY revenuewith ties, orROW_NUMBERwithout a tie-breaker, gives different answers on different runs. Fix: add a unique column to everyORDER BY.
Try it yourself
- Easy: For each payment method, show the number of payments and the total amount, largest total first.
- Medium: For each customer with at least one non-cancelled order, show the number of days between their first and their most recent non-cancelled order.
- Hard: For each order, show the order total and the share (%) it represents of that customer's total spend, using window functions only (no self-join). Exclude cancelled orders.
Answers
-- 1.
SELECT method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method
ORDER BY total DESC, method;
-- 2.
SELECT customer_id,
CAST(julianday(MAX(order_date)) - julianday(MIN(order_date)) AS INTEGER) AS days_between
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
ORDER BY customer_id;
-- 3.
WITH order_totals AS (
SELECT o.order_id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY o.order_id, o.customer_id
)
SELECT customer_id, order_id, total,
ROUND(100.0 * total / SUM(total) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM order_totals
ORDER BY customer_id, order_id;Summary
- Restate the question, fix the grain, list edge cases, build in CTE steps and check the result against a known number.
- NULLs, fan-out from joining two "many" tables, and
WHEREon a LEFT JOIN's right side cause most wrong answers. - Rank with
ROW_NUMBER/RANK/DENSE_RANKin a CTE, then filter; choose the function by how ties should behave. LAG, runningSUM() OVER, date minus row number (islands) and first-event cohorts cover most time-series questions.- Keep filters sargable, and in timed tests favour clear, checked answers over clever ones.
Next: Capstone Projects, Cheat Sheet and What's Next turns these skills into four portfolio projects, gives you a one-page reference for the advanced tutorial, and shows where the AI path goes from here.