🗄 SQL & Databases · Chapter 11

The 20 SQL interview questions that keep reappearing

SQL interviews recycle about eight shapes. Recognise the shape and the query writes itself.

Study this lesson free in the app →

The basics

SQL interview questions look endlessly varied, but nearly all of them are one of about eight recurring shapes — and each shape has a standard query you can write from memory.

The problem it solves: candidates grind a hundred problems and still freeze, because they're memorising answers instead of recognising patterns. "Latest order per customer", "deduplicate keeping the newest row" and "most recent status per device" are the same query in different words. Spot the shape in the first 15 seconds and you spend the rest of the round on edge cases — which is what's being graded.

The mental model: a doctor's diagnosis. Symptoms ("latest", "never", "consecutive", "as columns") point to a condition (top-N, anti-join, gaps-and-islands, pivot), and each condition has a known treatment. Say the diagnosis out loud before you write anything.

Explained

Candidates prepare by solving a hundred problems. Interviewers ask from a much smaller set.

The questions are endlessly reworded — second highest salary, latest order per customer, most recent status per device — but underneath there are only a handful of shapes. Learn to spot the shape in the first fifteen seconds and you spend your time on the edge cases instead of on the approach.

This lesson is the index to the rest of the track.

The eight shapes

| # | Shape | Tell | Tool | | --- | --- | --- | --- | | 1 | Top-N per group | "latest", "highest per", "second" | ROW_NUMBER + CTE | | 2 | Running / rolling | "cumulative", "7-day", "vs previous" | window frame, LAG | | 3 | Gaps & islands | "consecutive", "streak", "session" | LAG → flag → cumulative SUM | | 4 | Anti-join | "never", "missing", "didn't", "without" | NOT EXISTS / LEFT JOIN … IS NULL | | 5 | Pivot / conditional agg | "per status as columns" | SUM(CASE WHEN … THEN 1 ELSE 0 END) | | 6 | Dedup | "duplicates", "one row per" | ROW_NUMBER = shape 1 | | 7 | Self-join | "pairs", "manager", "compared to another row" | join the table to itself | | 8 | Funnel / ratio | "conversion", "% of" | conditional agg + NULLIF |

Notice that 1 and 6 are the same query. Deduplication is top-1-per-group. That collapse is typical — the eight shapes are fewer than they look.

Shapes 1–4, written out

-- 1 & 6. Top-N per group / deduplicate
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id
                               ORDER BY order_date DESC, order_id) rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

-- 2. Running total and previous-row comparison
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
       revenue - LAG(revenue) OVER (ORDER BY day)          AS change
FROM daily;

-- 3. Sessions (30-minute gap)
WITH flagged AS (
  SELECT *, CASE WHEN event_time - LAG(event_time) OVER w > INTERVAL '30 min'
                   OR LAG(event_time) OVER w IS NULL
                 THEN 1 ELSE 0 END AS is_new
  FROM events WINDOW w AS (PARTITION BY user_id ORDER BY event_time)
)
SELECT *, SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM flagged;

-- 4. Anti-join: customers who never ordered
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Shapes 5, 7 and 8

-- 5. Conditional aggregation (the pivot without PIVOT)
SELECT city,
       COUNT(*)                                             AS total,
       SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END)   AS completed,
       SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END)   AS cancelled,
       SUM(CASE WHEN status='completed' THEN fare ELSE 0 END) AS revenue
FROM rides GROUP BY city;

-- 7. Self-join: employees earning more than their manager
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- 8. Funnel / conversion rate — note NULLIF guarding the divide
SELECT city,
       COUNT(*)                                               AS requested,
       COUNT(*) FILTER (WHERE status='completed')             AS completed,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status='completed')
             / NULLIF(COUNT(*), 0), 1)                        AS conversion_pct
FROM rides GROUP BY city;

COUNT(*) FILTER (WHERE …) is the modern form of the CASE trick — cleaner where supported (Postgres, DuckDB, SQLite). NULLIF(x, 0) turns a zero denominator into NULL instead of an error, which is the standard guard for any ratio.

Decide the shape in 15 seconds

The whole skill is this: name the shape, name the edge case, then write. Most candidates start typing immediately and discover the shape three minutes in.

Match the question to the shape

Name the shape for each, without writing SQL.

1. Employees who earn more than their department average.
2. Customers who ordered in January but not in February.
3. The 3rd highest salary.
4. Longest streak of consecutive daily logins.
5. Percentage of rides completed, per city.
6. Products never ordered.
7. Each user's first and last order date.
8. Number of orders per customer, as columns per status.

How the round actually runs

A typical 45-minute SQL round has three or four questions of increasing difficulty, and the interviewer is watching process as much as output.

Minute 0–2. Ask about the data. "Can customer_id be null? Can a customer have two orders at the same timestamp? Roughly how many rows?" These aren't stalling — they determine whether you need a tiebreaker, a NULL guard, or to care about performance at all.

Minute 2–3. State the shape and the plan. "This is top-1-per-group, so I'll rank within customer by date descending and take rn = 1." If your approach is wrong, this is where you get corrected — cheaply.

Minute 3–10. Write it in CTEs. Named steps let the interviewer follow along and redirect you without a rewrite.

Then the follow-ups, which are the real assessment:

• "What if there are ties?" → the RANK/DENSE_RANK/ROW_NUMBER distinction
• "What if the table has a billion rows?" → partition pruning, filtering before the window, and whether this should be a pre-aggregated table
• "What if customer_id can be null?" → NULLs never match in a join and never equal anything
• "Can you do it without a window function?" → correlated subquery or self-join; sometimes asked because their engine is old

Nobody expects perfect syntax. Commas, dialect differences and function names are forgiven. What isn't forgiven is silence, a wrong shape defended for ten minutes, or ignoring a stated edge case.

Name the shape before you type. Top-N, running, gaps-and-islands, anti-join, conditional agg, self-join, funnel.

Dedup = top-1-per-group. Same query, different words.

Say the edge case out loud — ties, NULLs, empty groups — before you're asked.

Words to know

In two minutes

• Interviews recycle about eight shapes: top-N, running/rolling, gaps-and-islands, anti-join, conditional aggregation, dedup, self-join, funnel.
• Tell-words map to shapes: 'latest/highest per' → top-N; 'never/without' → anti-join; 'consecutive/streak' → gaps-and-islands.
• Dedup is top-1-per-group; always add a unique tiebreaker to ORDER BY.
• Ask about NULLs, duplicates, ties and scale in the first two minutes — they decide the query.
• Name the shape and the edge case out loud before typing; narration is part of the score.
• Classic traps: NOT IN with NULLs, RANK vs DENSE_RANK on ties, integer division in percentages, OFFSET on duplicates.
• Practise recognition: five differently worded problems per shape beat a hundred random ones.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.