Coding Exercises with Solutions

How to use this lesson#

Attempt each problem before reading the solution. Use the shop schema from earlier modules: customers, products, orders, order_items.

1. Second highest price#

Problem: Return the second highest distinct product price.

SELECT MAX(price) AS second_price
FROM products
WHERE price < (SELECT MAX(price) FROM products);

Reasoning: Take the maximum of everything below the maximum. It returns NULL if there's no second value, which is a fair answer. A general version for the Nth price uses DENSE_RANK():

SELECT DISTINCT price FROM (
  SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS r FROM products
) t WHERE r = 2;

DENSE_RANK, not ROW_NUMBER, because ties must share a rank.

2. Customers with no orders#

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Reasoning: NOT EXISTS is safe against NULLs and stops at the first match. The LEFT JOIN ... WHERE o.id IS NULL version is equivalent. Avoid NOT IN unless the subquery column is NOT NULL.

3. Find duplicate emails#

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Reasoning: Group on the value, then keep groups with more than one row. HAVING is required because the condition uses an aggregate.

4. Delete duplicates, keeping the lowest id#

DELETE c1
FROM customers c1
JOIN customers c2
  ON c1.email = c2.email AND c1.id > c2.id;

Reasoning: Each row is paired with any row of the same email and a smaller id, and those larger-id rows are deleted. MySQL allows multi-table DELETE syntax, naming the alias to delete from. Run it as a SELECT c1.* first.

5. Top 2 products per category by revenue#

WITH revenue AS (
  SELECT p.id, p.name, p.category,
         SUM(oi.qty * oi.unit_price) AS rev
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY rev DESC) AS rn
  FROM revenue
)
SELECT name, category, rev FROM ranked WHERE rn <= 2;

Reasoning: Aggregate first, then rank inside each category. Window functions can't go in WHERE, so the filter happens in the outer query. Use RANK instead if ties should both be included.

6. Running total of revenue by day#

WITH daily AS (
  SELECT DATE(o.ordered_at) AS d, SUM(oi.qty * oi.unit_price) AS rev
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY DATE(o.ordered_at)
)
SELECT d, rev,
       SUM(rev) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily
ORDER BY d;

Reasoning: Collapse to one row per day first, then run the window over those rows. The explicit ROWS frame avoids the default RANGE behavior with ties.

7. Customers who ordered in consecutive months#

WITH months AS (
  SELECT DISTINCT customer_id,
         TIMESTAMPDIFF(MONTH, '2000-01-01', ordered_at) AS m
  FROM orders
)
SELECT DISTINCT a.customer_id
FROM months a
JOIN months b ON b.customer_id = a.customer_id AND b.m = a.m + 1;

Reasoning: Convert each order date to a month number, so "next month" is just plus one. A self-join on the month number finds neighbors. A LEAD(m) OVER (...) version works too.

8. Orders above the customer's own average#

WITH totals AS (
  SELECT o.id, o.customer_id, SUM(oi.qty * oi.unit_price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id
)
SELECT id, customer_id, total
FROM (
  SELECT t.*, AVG(total) OVER (PARTITION BY customer_id) AS cust_avg FROM totals t
) x
WHERE total > cust_avg;

Reasoning: The window average sits beside each order without collapsing rows, then a simple filter compares them.

9. Pivot statuses into columns#

SELECT customer_id,
       SUM(status = 'new')       AS new_orders,
       SUM(status = 'paid')      AS paid_orders,
       SUM(status = 'shipped')   AS shipped_orders,
       SUM(status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id;

Reasoning: MySQL has no PIVOT keyword. Boolean expressions evaluate to 1 or 0, so summing them counts matches per group.

10. Why is this query slow, and how do you fix it?#

SELECT * FROM orders WHERE YEAR(ordered_at) = 2025 AND customer_id = 42;

Reasoning: The function on ordered_at prevents range use on that column. Rewrite and add a composite index:

SELECT id, status, ordered_at FROM orders
WHERE customer_id = 42
  AND ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01';

CREATE INDEX idx_orders_cust_date ON orders (customer_id, ordered_at);

Equality column first, range column second. Selecting fewer columns can also let the index cover the query.

11. Transfer money safely#

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
-- check ROW_COUNT() = 1, otherwise ROLLBACK
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Reasoning: The row lock plus the balance >= 100 guard stops overdrafts under concurrent transfers. Lock accounts in a consistent order (lowest id first) to avoid deadlocks.

Practice#

Without looking at the solutions, solve problems 5, 7, and 8 again on a fresh seed with edge cases: tied revenues, a customer with one order, and an empty category. Check your results by hand on the small data, then explain each solution aloud as if to an interviewer.