SQL Interview Patterns

The six query shapes that cover most interview and LeetCode SQL questions.

• Advanced
• 20 min read
• 3 objectives

Before this lessonLesson 16: Indexes and Query Performance

Interview SQL questions are written to look different from each other and are not. Almost all of them are one of six shapes. Learn to recognise the shape and the query writes itself.

Pattern 1 — Nth highest

Two approaches. LIMIT ... OFFSET is shortest but returns nothing (not NULL) when there are too few rows. Wrapping it in a scalar subquery fixes that, which is what LeetCode 176 asks for.

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

salary
-------
150000

SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1)
       AS second_highest;

second_highest
---------------
150000

The DENSE_RANK version generalises to "per department" and to "the Nth highest for every group at once":

WITH ranked AS (
  SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT salary AS second_highest FROM ranked WHERE rnk = 2;

second_highest
---------------
150000

Pattern 2 — Find and delete duplicates

Finding them is GROUP BY plus HAVING COUNT(*) > 1. Deleting them means keeping the smallest id per group — LeetCode 182 and 196.

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

email           | n
----------------+--
ada@example.com | 2

DELETE FROM person
WHERE id NOT IN (SELECT MIN(id) FROM person GROUP BY email);

SELECT id, email FROM person ORDER BY id;

1 row affected.

id | email
---+-------------------
1  | ada@example.com
2  | linus@example.com
4  | GRACE@example.com

Pattern 3 — Top N per group

Rank inside a partition, then filter outside. Change = 1 to <= 3 and you have a top-three-per-group report.

WITH ranked AS (
  SELECT department, name, 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, salary DESC;

department  | name     | salary
------------+---------+-------
Design      | Margaret | 140000
Design      | Alan     | 110000
Design      | Barbara  | 110000
Engineering | Grace    | 190000
Engineering | Ada      | 150000

Pattern 4 — Compare a row to its neighbour

"Higher than yesterday", "three logins in a row", "time between events" — all of them are LAG/LEAD, or a self-join on an offset key when windows are unavailable.

WITH compared AS (
  SELECT id, record_date, temperature,
         LAG(temperature) OVER (ORDER BY record_date) AS yesterday
  FROM weather
)
SELECT id FROM compared WHERE temperature > yesterday;

id
---
2
4

SELECT w1.id
FROM weather w1
JOIN weather w2 ON julianday(w1.record_date) - julianday(w2.record_date) = 1
WHERE w1.temperature > w2.temperature;

id
---
2
4

Pattern 5 — Pivot with conditional aggregation

Anything phrased as "report X and Y as separate columns" is SUM(CASE WHEN ...), one branch per column.

SELECT strftime('%Y-%m', trans_date) AS month,
       country,
       COUNT(*) AS trans_count,
       SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
       SUM(amount) AS trans_total,
       SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total
FROM transactions
GROUP BY month, country
ORDER BY month, country;

month   | country | trans_count | approved_count | trans_total | approved_total
--------+--------+------------+---------------+------------+---------------
2018-12 | US      | 2           | 1              | 3000        | 1000
2019-01 | DE      | 2           | 1              | 2500        | 2000
2019-01 | US      | 1           | 1              | 2000        | 2000

Pattern 6 — The anti-join: who never did X

Three spellings of one idea. NOT EXISTS is the safest, LEFT JOIN ... IS NULL the most common, NOT IN the one that breaks on NULL.

SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM visits v
LEFT JOIN purchases p ON p.visit_id = v.visit_id
WHERE p.purchase_id IS NULL
GROUP BY v.customer_id
ORDER BY v.customer_id;

customer_id | count_no_trans
------------+---------------
30          | 1
54          | 2

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

name
------
Linus

A checklist for the interview itself

1. Say the shape out loud first: "this is a top-N-per-group".
2. Ask what should happen with ties and with missing rows — it is the point of the question.
3. Name intermediate steps with a CTE; nobody has ever complained a query was too readable.
4. State the assumption you made about NULL before they ask.
5. Mention the index that would support your WHERE clause.

Key takeaways

• Nth highest: OFFSET for one value, DENSE_RANK to generalise.
• Duplicates: GROUP BY ... HAVING COUNT(*) > 1, keep MIN(id).
• Top N per group and neighbour comparisons are window functions.
• "Never did X" is NOT EXISTS or LEFT JOIN ... IS NULL.

-- Write your solution here