Autocoder

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.

Tablescustomers (5 rows), orders (9 rows)
Problems14, easy to hard
DialectsStandard 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.

sql
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

SQL Query Practice: questions with worked answers

  1. 1

    List customers who have never placed an order.

    easy

    Anti-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.

    sql
    SELECT c.cname FROM customers c LEFT JOIN orders o ON o.cust_id = c.cust_id WHERE o.order_id IS NULL;
    -- cname
    -- -----
    -- Vik
  2. 2

    Show total spend and number of orders per customer, including customers with none.

    easy

    LEFT JOIN from customers so Vik stays, COUNT the order id (not *), and COALESCE the sum to 0.

    sql
    SELECT 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      | 0
  3. 3

    Find the top 2 orders by amount in each category.

    medium

    Number rows per category with ROW_NUMBER (or RANK if ties should be kept) and filter in an outer query.

    sql
    SELECT 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  | 55000
  4. 4

    Compute monthly revenue and the change from the previous month.

    medium

    Aggregate 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.

    sql
    SELECT 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   | -53000
  5. 5

    Show a running total of revenue by order date.

    medium

    SUM 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.

    sql
    SELECT 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   | 200800
  6. 6

    Pivot: show revenue per customer split into Tech and Home columns.

    medium

    Conditional 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.

    sql
    SELECT 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     | 0
  7. 7

    Find customers who bought in both categories.

    medium

    Group 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.

    sql
    SELECT 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
    -- -----
    -- Sana
  8. 8

    Find the second order of each customer (by date).

    medium

    ROW_NUMBER partitioned by customer ordered by date, then keep 2. Customers with fewer than two orders are not returned.

    sql
    SELECT 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-18
  9. 9

    List cities with more than one customer, and the customer names in one cell.

    medium

    GROUP 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.

    sql
    SELECT 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,Tej
  10. 10

    Find customers whose spending is above the average customer spending.

    hard

    Compute 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.

    sql
    WITH 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  | 90800
  11. 11

    Swap the city values of Pune and Delhi in a single UPDATE.

    medium

    A 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.

    sql
    UPDATE customers
    SET city = CASE city WHEN 'Pune' THEN 'Delhi' WHEN 'Delhi' THEN 'Pune' ELSE city END
    WHERE city IN ('Pune', 'Delhi');
  12. 12

    Show each customer first and last order date and days between.

    medium

    MIN 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.

    sql
    SELECT 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 | 15
  13. 13

    Which product has the highest revenue in each month?

    hard

    Aggregate 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.

    sql
    SELECT 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   | 28000
  14. 14

    Delete duplicate customers by name, keeping the lowest id.

    medium

    Keep 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

See all in this section