Home›SQL›MySQL Interview – Advanced Queries

MySQL Interview – Advanced Queries

January 16, 20255 min readUpdated 8/25/2026

The query-writing questions. Each one is solved against the pizza database — 18 orders, so you can check the answers by eye — and every result below came from running it.

1. The second-highest value

SELECT MAX(total) AS second_highest FROM customer_order
WHERE  total < (SELECT MAX(total) FROM customer_order);
+----------------+
| second_highest |
+----------------+
|          40.86 |
+----------------+

"The largest value smaller than the largest value." It handles ties correctly — if two orders share the top total, this still returns the next distinct value — and it returns NULL rather than erroring when there is no second value.

ORDER BY total DESC LIMIT 1 OFFSET 1 also works and answers a subtly different question: it gives the second row, so with a tie at the top it returns the top value again. Know which one you were asked for.

2. Nth highest, with window functions

SELECT id, total, dense_rank_ FROM (
  SELECT id, total, DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rank_
  FROM customer_order) r
WHERE  dense_rank_ = 2 ORDER BY id;
+----+-------+-------------+
| id | total | dense_rank_ |
+----+-------+-------------+
| 17 | 40.86 |           2 |
+----+-------+-------------+

Change the 2 for any N. The interviewer is usually checking three things: that you reach for a window function, that you know DENSE_RANK is right here because RANK skips after a tie, and that you know a window function cannot go in WHERE — so it has to be computed in a subquery or CTE and filtered outside. Window functions

3. Find duplicates

SELECT total, COUNT(*) AS n, GROUP_CONCAT(id ORDER BY id) AS ids
FROM   customer_order GROUP BY total HAVING COUNT(*) > 1 ORDER BY total;
+-------+---+------+
| total | n | ids  |
+-------+---+------+
| 18.43 | 2 | 2,14 |
| 32.18 | 2 | 3,4  |
+-------+---+------+

GROUP BY the columns that define a duplicate, then HAVING COUNT(*) > 1. The GROUP_CONCAT is what makes it actionable — you get the ids, not just a count.

...and delete them, keeping one

-- session 1: destructive. Shown, not run.
DELETE d FROM customer_order d
JOIN   (SELECT total, MIN(id) AS keep_id FROM customer_order
        GROUP BY total HAVING COUNT(*) > 1) k
  ON   d.total = k.total AND d.id > k.keep_id;

MySQL will not let you DELETE from a table you are selecting from in a subquery, so the join form is the answer. d.id > k.keep_id keeps the lowest id of each group. Run it as a SELECT first — see running queries in production.

4. Gaps in a sequence

WITH RECURSIVE seq AS (
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 20
)
SELECT s.n AS missing_id FROM seq s
LEFT   JOIN customer_order o ON o.id = s.n
WHERE  o.id IS NULL ORDER BY s.n;
+------------+
| missing_id |
+------------+
|         19 |
|         20 |
+------------+

Generate the complete series, left join the real rows onto it, keep the ones with no match. The same shape solves "show every day in the month including days with no orders", which is the version that actually comes up at work — a GROUP BY date simply produces no row for an empty day and the chart joins across the gap. CTEs

Without a recursive CTE you would need a numbers table, which is worth mentioning as the pre-MySQL-8 answer.

5. Rows in one table with no match in another

-- the anti-join
SELECT p.id, p.name FROM product p
LEFT   JOIN order_item i ON i.product_id = p.id
WHERE  i.id IS NULL;

Three ways exist and they are not equivalent. LEFT JOIN ... IS NULL and NOT EXISTS both work correctly. NOT IN returns nothing at all if the subquery yields a single NULL — and order_item.product_id is nullable, so that is a live risk here rather than a theoretical one. Volunteering that is the whole point of the question. Subqueries

6. A pivot

SELECT order_type,
       SUM(status = 'COMPLETED') AS completed,
       SUM(status = 'CANCELLED') AS cancelled,
       COUNT(*) AS total_orders
FROM   customer_order GROUP BY order_type ORDER BY order_type;
+------------+-----------+-----------+--------------+
| order_type | completed | cancelled | total_orders |
+------------+-----------+-----------+--------------+
| CARRYOUT   |         5 |         1 |            7 |
| DELIVERY   |         8 |         1 |           11 |
+------------+-----------+-----------+--------------+

Rows into columns, in one pass. SUM(condition) works because a comparison is 1 or 0; SUM(CASE WHEN … THEN 1 ELSE 0 END) is the portable spelling. Use SUM, not COUNT — COUNT(CASE … ELSE 0 END) counts every row, because 0 is not NULL.

The honest caveat to add: SQL cannot pivot on values it does not know at parse time. A column per status means naming each status. Dynamic pivots need generated SQL or the application. IF and CASE

7. A running total

SELECT id, total, SUM(total) OVER (ORDER BY id) AS running_total
FROM   customer_order WHERE status = 'COMPLETED' ORDER BY id LIMIT 5;

Adding ORDER BY to OVER defaults the frame to "start of partition to current row", which is what makes it cumulative rather than a repeated grand total. The pre-8.0 answer was a self join on a.id <= b.id, which is quadratic — worth mentioning to show you know why window functions were added.

8. Month-over-month growth

WITH monthly AS (
    SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
    FROM   customer_order WHERE status = 'COMPLETED'
    GROUP  BY month
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       ROUND(LAG(revenue) OVER (ORDER BY month), 2) AS previous,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM   monthly ORDER BY month;

Aggregate to months in a CTE, then LAG to reach the previous row. Two details worth saying aloud: '%Y-%m' sorts correctly as a string, where MONTH() alone merges every January in your history; and NULLIF(…, 0) turns a zero denominator into NULL instead of an error.

9. Top N per group

WITH ranked AS (
    SELECT product_name, size, line_total,
           ROW_NUMBER() OVER (PARTITION BY product_name ORDER BY line_total DESC, id) AS rn
    FROM   order_item
)
SELECT product_name, size, line_total FROM ranked WHERE rn <= 2
ORDER  BY product_name, rn;

The classic. PARTITION BY restarts the numbering per group, and the filter goes outside because a window function cannot appear in WHERE. Note the , id tiebreaker — without it, rows with equal line_total are ordered arbitrarily and the query returns different rows on different runs.

What they are really testing

  • Do you reach for set operations or for loops? A cursor where a join would do is the answer they are watching for.
  • Do you think about NULL? The NOT IN trap and COUNT(col) versus COUNT(*) come up constantly.
  • Do you think about ties? Second-highest, top-N and pagination all change answer depending on how you handle them.
  • Do you know MySQL 8? CTEs and window functions have been available for years, and reaching for a correlated subquery where a window function is natural reads as out of date.
  • Would this survive production? Saying "I would EXPLAIN this before running it on a large table" is usually worth more than the query.

Say your assumptions out loud, and ask what should happen on ties, on NULLs and on empty groups. That conversation is most of what is being assessed.