SQL Query Practice: Interview Questions with Solutions
Last updated 2026-11-19
This page is a practice set of the query problems that appear again and again in SQL rounds: customers with no orders, top N per group, month-over-month change, running totals, pivoting and finding duplicates. Each problem comes with the table, a solution and the real result, so you can cover the answer, write your own query and compare.
All queries run on the two small tables below. Standard SQL is used wherever possible, and where MySQL and PostgreSQL differ (date functions, mostly) the note says so. Work through them in order: the early ones are warm-ups and the last few combine joins, aggregates and window functions. For deeper explanations of each building block, see SQL joins and SQL window functions, and for round strategy see how to crack a SQL interview.
| Tables | customers (5 rows), orders (9 rows) |
|---|---|
| Problems | 14, easy to hard |
| Dialects | Standard SQL with MySQL and PostgreSQL notes |
Sample data
Vik has placed no orders, and Tej has a single one. The orders span January to March 2026 across two categories, Tech and Home.
CREATE TABLE customers (cust_id INT PRIMARY KEY, cname VARCHAR(10), city VARCHAR(10));
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT, product VARCHAR(10), category VARCHAR(10), amount INT, order_date DATE);
INSERT INTO customers VALUES (1,'Ravi','Pune'),(2,'Sana','Delhi'),(3,'Tej','Pune'),(4,'Uma','Mumbai'),(5,'Vik','Delhi');
INSERT INTO orders VALUES
(101,1,'Laptop','Tech',60000,'2026-01-10'),(102,1,'Mouse','Tech',800,'2026-01-15'),(103,2,'Desk','Home',9000,'2026-01-20'),
(104,2,'Laptop','Tech',55000,'2026-02-02'),(105,3,'Chair','Home',7000,'2026-02-14'),(106,1,'Phone','Tech',30000,'2026-02-20'),
(107,4,'Lamp','Home',1500,'2026-03-03'),(108,2,'Phone','Tech',28000,'2026-03-09'),(109,4,'Desk','Home',9500,'2026-03-18');A routine for any SQL problem
- Restate the output: which columns, one row per what?
- Pick the grain: if the output is one row per customer, you will GROUP BY customer.
- Decide which table must be preserved, which tells you the join direction.
- Write it in steps with a CTE if it needs more than one level.
- Test with the edge case in the data, such as the customer with no orders.
SQL Query Practice: questions with worked answers
1 List customers who have never placed an order.
easyAnti-join: LEFT JOIN and keep rows where the right side is NULL, or NOT EXISTS. Vik is the only customer with no rows in orders.
sqlSELECT c.cname FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id WHERE o.order_id IS NULL; -- cname -- ----- -- Vik2 Show total spend and number of orders per customer, including customers with none.
easyLEFT JOIN from customers so Vik stays, COUNT the order id (not *), and COALESCE the sum to 0.
sqlSELECT c.cname, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS spend FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id GROUP BY c.cname ORDER BY spend DESC; -- cname | orders | spend -- ------+--------+------ -- Sana | 3 | 92000 -- Ravi | 3 | 90800 -- Uma | 2 | 11000 -- Tej | 1 | 7000 -- Vik | 0 | 03 Find the top 2 orders by amount in each category.
mediumNumber rows per category with ROW_NUMBER (or RANK if ties should be kept) and filter in an outer query.
sqlSELECT category, product, amount FROM ( SELECT category, product, amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rn FROM orders ) t WHERE rn <= 2 ORDER BY category, amount DESC; -- category | product | amount -- ---------+---------+------- -- Home | Desk | 9500 -- Home | Desk | 9000 -- Tech | Laptop | 60000 -- Tech | Laptop | 550004 Compute monthly revenue and the change from the previous month.
mediumAggregate by month first, then apply LAG over the monthly rows. The first month has no previous value, so the change is NULL. Month extraction differs: strftime here, DATE_FORMAT(order_date, '%Y-%m') in MySQL, TO_CHAR(order_date, 'YYYY-MM') in PostgreSQL.
sqlSELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM ( SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY strftime('%Y-%m', order_date) ) m ORDER BY month; -- month | revenue | change -- --------+---------+------- -- 2026-01 | 69800 | NULL -- 2026-02 | 92000 | 22200 -- 2026-03 | 39000 | -530005 Show a running total of revenue by order date.
mediumSUM over an ordered window. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit, and add order_id as a tie-breaker if two orders can share a date.
sqlSELECT order_id, order_date, amount, SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running FROM orders ORDER BY order_date, order_id; -- order_id | order_date | amount | running -- ---------+------------+--------+-------- -- 101 | 2026-01-10 | 60000 | 60000 -- 102 | 2026-01-15 | 800 | 60800 -- 103 | 2026-01-20 | 9000 | 69800 -- 104 | 2026-02-02 | 55000 | 124800 -- 105 | 2026-02-14 | 7000 | 131800 -- 106 | 2026-02-20 | 30000 | 161800 -- 107 | 2026-03-03 | 1500 | 163300 -- 108 | 2026-03-09 | 28000 | 191300 -- 109 | 2026-03-18 | 9500 | 2008006 Pivot: show revenue per customer split into Tech and Home columns.
mediumConditional aggregation with CASE inside SUM. It works in every database, whereas PIVOT is specific to SQL Server and Oracle and PostgreSQL offers crosstab from an extension.
sqlSELECT c.cname, COALESCE(SUM(CASE WHEN o.category = 'Tech' THEN o.amount END), 0) AS tech, COALESCE(SUM(CASE WHEN o.category = 'Home' THEN o.amount END), 0) AS home FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id GROUP BY c.cname ORDER BY c.cname; -- cname | tech | home -- ------+-------+------ -- Ravi | 90800 | 0 -- Sana | 83000 | 9000 -- Tej | 0 | 7000 -- Uma | 0 | 11000 -- Vik | 0 | 07 Find customers who bought in both categories.
mediumGroup by customer and require two distinct categories in HAVING. A self join or INTERSECT also works, but this is the shortest. Only Sana has bought from both Tech and Home.
sqlSELECT c.cname FROM customers c JOIN orders o ON o.cust_id = c.cust_id GROUP BY c.cname HAVING COUNT(DISTINCT o.category) = 2 ORDER BY c.cname; -- cname -- ----- -- Sana8 Find the second order of each customer (by date).
mediumROW_NUMBER partitioned by customer ordered by date, then keep 2. Customers with fewer than two orders are not returned.
sqlSELECT cust_id, order_id, order_date FROM ( SELECT cust_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY order_date) AS rn FROM orders ) t WHERE rn = 2 ORDER BY cust_id; -- cust_id | order_id | order_date -- --------+----------+----------- -- 1 | 102 | 2026-01-15 -- 2 | 104 | 2026-02-02 -- 4 | 109 | 2026-03-189 List cities with more than one customer, and the customer names in one cell.
mediumGROUP BY city with HAVING COUNT(*) > 1. String aggregation differs: GROUP_CONCAT in MySQL and SQLite, STRING_AGG(cname, ', ') in PostgreSQL and SQL Server, LISTAGG in Oracle.
sqlSELECT city, COUNT(*) AS n, GROUP_CONCAT(cname) AS names FROM customers GROUP BY city HAVING COUNT(*) > 1 ORDER BY city; -- city | n | names -- ------+---+--------- -- Delhi | 2 | Sana,Vik -- Pune | 2 | Ravi,Tej10 Find customers whose spending is above the average customer spending.
hardCompute per-customer totals in a CTE, then compare each with the average of those totals (not the average order). Using the average order amount would answer a different question.
sqlWITH spend AS (SELECT cust_id, SUM(amount) AS total FROM orders GROUP BY cust_id) SELECT c.cname, s.total FROM spend s JOIN customers c ON c.cust_id = s.cust_id WHERE s.total > (SELECT AVG(total) FROM spend) ORDER BY s.total DESC; -- cname | total -- ------+------ -- Sana | 92000 -- Ravi | 9080011 Swap the city values of Pune and Delhi in a single UPDATE.
mediumA CASE expression evaluates every row against the old values, so the swap is safe in one statement. In MySQL and PostgreSQL the same pattern works; do not run two updates in sequence, or all rows end up in one city.
sqlUPDATE customers SET city = CASE city WHEN 'Pune' THEN 'Delhi' WHEN 'Delhi' THEN 'Pune' ELSE city END WHERE city IN ('Pune', 'Delhi');12 Show each customer first and last order date and days between.
mediumMIN and MAX per customer, then subtract. Date subtraction differs: julianday(max) - julianday(min) in SQLite, DATEDIFF(max, min) in MySQL, and max - min (an integer for dates) in PostgreSQL. Customers with one order show 0.
sqlSELECT cust_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order, CAST(julianday(MAX(order_date)) - julianday(MIN(order_date)) AS INT) AS days_between FROM orders GROUP BY cust_id ORDER BY cust_id; -- cust_id | first_order | last_order | days_between -- --------+-------------+------------+------------- -- 1 | 2026-01-10 | 2026-02-20 | 41 -- 2 | 2026-01-20 | 2026-03-09 | 48 -- 3 | 2026-02-14 | 2026-02-14 | 0 -- 4 | 2026-03-03 | 2026-03-18 | 1513 Which product has the highest revenue in each month?
hardAggregate revenue per month and product, then rank within each month. RANK keeps ties; use ROW_NUMBER for a single winner. This combines GROUP BY with a window function over the grouped result, a pattern worth practising.
sqlSELECT month, product, revenue FROM ( SELECT strftime('%Y-%m', order_date) AS month, product, SUM(amount) AS revenue, RANK() OVER (PARTITION BY strftime('%Y-%m', order_date) ORDER BY SUM(amount) DESC) AS r FROM orders GROUP BY strftime('%Y-%m', order_date), product ) t WHERE r = 1 ORDER BY month; -- month | product | revenue -- --------+---------+-------- -- 2026-01 | Laptop | 60000 -- 2026-02 | Laptop | 55000 -- 2026-03 | Phone | 2800014 Delete duplicate customers by name, keeping the lowest id.
mediumKeep the minimum id per name and delete the rest. MySQL cannot delete from a table while selecting from it in a plain subquery, so use a join delete or wrap the subquery in a derived table. PostgreSQL accepts the NOT IN form directly. On this data there are no duplicates, so nothing is deleted, which is worth stating before running it.
sql-- PostgreSQL DELETE FROM customers WHERE cust_id NOT IN (SELECT MIN(cust_id) FROM customers GROUP BY cname); -- MySQL DELETE c1 FROM customers c1 JOIN customers c2 ON c1.cname = c2.cname AND c1.cust_id > c2.cust_id;
Frequently asked questions
How many SQL problems should I practise before an interview?
Aim to solve the common patterns well rather than count problems: anti-join, top N per group, running total, pivot, duplicates and month-over-month change.
Which SQL dialect do interviews use?
Usually MySQL or PostgreSQL, sometimes SQL Server or Oracle for specific companies. Check the job description and the test platform.
Do I need to memorise date functions?
Know the basics for your target dialect: truncating to month, difference between dates, and adding intervals. They differ across MySQL, PostgreSQL and SQL Server.
Where can I find the theory behind these queries?
Start with SQL group by and having and SQL subqueries.
Related guides
More in Programming Language Interview Questions by Language
- SQL Subqueries Interview Questions
- SQL Window Functions Interview Questions
- TypeScript Interview Questions and Answers
- .NET Core and .NET 8 Interview Questions
- Advanced Python Interview Questions
- ASP.NET Core MVC Interview Questions
- C Language Interview Questions and Answers
- C Pointers Interview Questions
- C Programming Output Questions with Answers
- C# Async Await Interview Questions
- C# Coding Interview Questions
- C# Collections Interview Questions