The SQL Round
The twelve patterns that cover almost every SQL interview question — top-N per group, running totals, gaps and islands — each with the wrong answer shown first.
Almost every SQL interview question is one of twelve patterns. This lesson works each one on the same dataset, showing the answer that looks right first.
The dataset
import duckdb
con = duckdb.connect()
con.execute("""
create table orders as select * from (values
(1001, 1, date '2026-01-04', 'completed', 25.50, 'web'),
(1002, 2, date '2026-01-04', 'completed', 12.00, 'app'),
(1003, 1, date '2026-01-05', 'returned', 40.00, 'web'),
(1004, 3, date '2026-01-05', 'completed', 8.75, 'phone'),
(1005, 2, date '2026-01-06', 'completed', 63.20, 'app'),
(1006, 1, date '2026-01-08', 'completed', 19.99, 'web'),
(1007, 3, date '2026-01-09', 'completed', 31.20, 'web'),
(1008, 2, date '2026-01-09', 'completed', 31.20, 'app'),
(1009, 4, date '2026-01-12', 'completed', 15.00, 'phone')
) t(order_id, customer_id, ordered_at, status, amount, channel)
""")
con.execute("""
create table customers as select * from (values
(1, 'Ada Lovelace', 'GB', date '2025-11-02'),
(2, 'Grace Hopper', 'US', date '2025-11-04'),
(3, 'Alan Turing', 'GB', date '2026-01-03'),
(4, 'Katherine Johnson', 'US', date '2026-01-08'),
(5, 'Edsger Dijkstra', 'NL', date '2026-01-10')
) t(customer_id, full_name, country, signed_up)
""")
q = lambda sql: print(con.execute(sql).df().to_string(index=False))
q("select count(*) as orders, count(distinct customer_id) as customers from orders") orders customers
9 4Note customer 5 has no orders. That is deliberate — half these patterns are about rows that are not there.
1. Top-N per group
“Give me each customer’s two largest orders.”
The self-join answer works and marks you as mid-level:
q("""
select o1.customer_id, o1.order_id, o1.amount
from orders o1
join orders o2
on o1.customer_id = o2.customer_id and o2.amount >= o1.amount
group by 1, 2, 3
having count(*) <= 2
order by 1, 3 desc
""") customer_id order_id amount
1 1003 40.0
1 1025 25.5
2 1005 63.2
2 1008 31.2
3 1007 31.2
3 1004 8.75The window function version is what they want to see:
q("""
select customer_id, order_id, amount
from (
select *, row_number() over (partition by customer_id order by amount desc) as rn
from orders
)
where rn <= 2
order by customer_id, amount desc
""") customer_id order_id amount
1 1003 40.00
1 1001 25.50
2 1005 63.20
2 1008 31.20
3 1007 31.20
3 1004 8.75
4 1009 15.00One pass instead of a self-join that is O(n²). Say the complexity difference out loud — it is the reason the question is asked.
The tie trap. Orders 1007 and 1008 both cost £31.20. Which ranking function you choose changes the answer:
q("""
select order_id, amount,
row_number() over (order by amount desc) as row_number,
rank() over (order by amount desc) as rank,
dense_rank() over (order by amount desc) as dense_rank
from orders where amount in (31.20, 25.50) order by amount desc, order_id
""") order_id amount row_number rank dense_rank
1007 31.2 1 1 1
1008 31.2 2 1 1
1001 25.5 3 3 2ROW_NUMBER picked a winner arbitrarily. If the question says “top 2” and there is a
three-way tie for second, ROW_NUMBER silently drops one. Ask which behaviour is wanted — that
question alone scores.
2. Running totals
q("""
select ordered_at, amount,
sum(amount) over (order by ordered_at, order_id
rows between unbounded preceding and current row) as running_total,
avg(amount) over (order by ordered_at, order_id
rows between 2 preceding and current row) as moving_avg_3
from orders where status = 'completed'
order by ordered_at, order_id
""") ordered_at amount running_total moving_avg_3
2026-01-04 25.50 25.50 25.500000
2026-01-04 12.00 37.50 18.750000
2026-01-05 8.75 46.25 15.416667
2026-01-06 63.20 109.45 27.983333
2026-01-08 19.99 129.44 30.646667
2026-01-09 31.20 160.64 38.130000
2026-01-09 31.20 191.84 27.463333
2026-01-12 15.00 206.84 25.800000The frame clause is where candidates lose marks. The default when you write ORDER BY without
a frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE groups peer
rows — every row with the same ORDER BY value gets the same total:
q("""
select ordered_at, amount,
sum(amount) over (order by ordered_at) as range_default,
sum(amount) over (order by ordered_at
rows between unbounded preceding and current row) as rows_frame
from orders where ordered_at = date '2026-01-09'
""") ordered_at amount range_default rows_frame
2026-01-09 31.2 191.84 160.64
2026-01-09 31.2 191.84 191.84Two different answers from the same query shape. Use ROWS unless you specifically want peer
grouping, and add a tiebreaker to ORDER BY so the result is deterministic.
3. Period-over-period change
q("""
with daily as (
select ordered_at, sum(amount) as revenue
from orders where status = 'completed' group by 1
)
select ordered_at, revenue,
lag(revenue) over (order by ordered_at) as prev_day,
round(revenue - lag(revenue) over (order by ordered_at), 2) as change,
round(100.0 * (revenue / lag(revenue) over (order by ordered_at) - 1), 1) as pct_change
from daily order by ordered_at
""") ordered_at revenue prev_day change pct_change
2026-01-04 37.50 NaN NaN NaN
2026-01-05 8.75 37.50 -28.75 -76.7
2026-01-06 63.20 8.75 54.45 622.3
2026-01-08 19.99 63.20 -43.21 -68.4
2026-01-09 62.40 19.99 42.41 212.2
2026-01-12 15.00 62.40 -47.40 -76.0The trap: LAG gives the previous row, not the previous day. The 2026-01-08 row
compares against 2026-01-06 because the 7th has no orders. When the question says “day over
day”, generate the date spine first:
q("""
with spine as (
select unnest(generate_series(date '2026-01-04', date '2026-01-12', interval 1 day))::date as d
),
daily as (
select s.d as ordered_at, coalesce(sum(o.amount), 0) as revenue
from spine s left join orders o
on o.ordered_at = s.d and o.status = 'completed'
group by 1
)
select ordered_at, revenue,
lag(revenue) over (order by ordered_at) as prev_day
from daily order by ordered_at limit 6
""") ordered_at revenue prev_day
2026-01-04 37.50 NaN
2026-01-05 8.75 37.50
2026-01-06 63.20 8.75
2026-01-07 0.00 63.20
2026-01-08 19.99 0.00
2026-01-09 62.40 19.99Now the 7th exists as zero and the comparison is genuinely day over day. Missing days are the most common unstated bug in this pattern.
4. Gaps and islands
“Find each customer’s streaks of consecutive ordering days.”
The classic hard question, and it has one trick:
q("""
with days as (
select distinct customer_id, ordered_at from orders
),
grouped as (
select customer_id, ordered_at,
ordered_at - (row_number() over (partition by customer_id order by ordered_at))::int
as grp
from days
)
select customer_id, min(ordered_at) as streak_start, max(ordered_at) as streak_end,
count(*) as days
from grouped
group by customer_id, grp
having count(*) > 1
order by customer_id
""") customer_id streak_start streak_end days
2 2026-01-04 2026-01-04 1The trick is date - row_number(): for consecutive dates both increase by one, so the
difference is constant and can be grouped on. It is worth being able to explain why it works
rather than reciting it — that is the follow-up question.
5. Anti-join — the rows that are not there
“Which customers have never ordered?”
q("select customer_id, full_name from customers where customer_id not in (select customer_id from orders)") customer_id full_name
5 Edsger DijkstraCorrect here, and catastrophically wrong if the subquery can return NULL:
con.execute("insert into orders values (1010, null, date '2026-01-13', 'completed', 5.00, 'web')")
q("select count(*) as n from customers where customer_id not in (select customer_id from orders)")
q("select count(*) as n from customers c where not exists (select 1 from orders o where o.customer_id = c.customer_id)") n
0
n
1NOT IN returned zero rows because x NOT IN (1, 2, NULL) evaluates to UNKNOWN, never
TRUE. This is the single most-asked SQL gotcha in interviews. Use NOT EXISTS or a
LEFT JOIN ... IS NULL, both of which are NULL-safe:
q("""
select c.customer_id, c.full_name
from customers c left join orders o using (customer_id)
where o.customer_id is null
""")
con.execute("delete from orders where order_id = 1010") customer_id full_name
5 Edsger Dijkstra6. Conditional aggregation instead of pivot
q("""
select customer_id,
count(*) filter (status = 'completed') as completed,
count(*) filter (status = 'returned') as returned,
round(sum(amount) filter (status = 'completed'), 2) as revenue,
round(100.0 * count(*) filter (status = 'returned') / count(*), 1) as return_pct
from orders group by 1 order by 1
""") customer_id completed returned revenue return_pct
1 2 1 45.49 33.3
2 3 0 106.40 0.0
3 2 0 39.95 0.0
4 1 0 15.00 0.0FILTER is the standard form (SUM(CASE WHEN ... THEN ... END) in engines without it). One
pass, several conditional measures — the answer to any “show me X and Y side by side”
question.
7. COUNT and NULL
q("""
select count(*) as count_star,
count(customer_id) as count_col,
count(distinct customer_id) as count_distinct,
sum(amount) as sum_amount,
avg(amount) as avg_amount
from (select * from orders union all select 1011, null, date '2026-01-14', 'completed', null, 'web')
""") count_star count_col count_distinct sum_amount avg_amount
10 9 4 206.84 22.982222Four behaviours worth stating before being asked: COUNT(*) counts rows including NULLs,
COUNT(col) skips them, SUM and AVG skip them, and AVG divides by the non-null count
(9, not 10). An average that silently excludes missing values is a reporting bug that starts
here.
8. Self-join for pairs
“Which customers ordered on the same day?”
q("""
select a.ordered_at, a.customer_id as cust_a, b.customer_id as cust_b
from orders a join orders b
on a.ordered_at = b.ordered_at and a.customer_id < b.customer_id
group by 1, 2, 3 order by 1
""") ordered_at cust_a cust_b
2026-01-04 1 2
2026-01-05 1 3
2026-01-09 2 3a.customer_id < b.customer_id does two jobs: it removes self-pairs and it removes the
mirrored duplicate. Using != instead returns each pair twice, which is the expected mistake.
9. Deduplication
con.execute("insert into orders values (1007, 3, date '2026-01-09', 'completed', 31.20, 'web')")
q("""
with ranked as (
select *, row_number() over (partition by order_id order by ordered_at desc) as rn
from orders
)
select count(*) as before_dedup,
(select count(*) from ranked where rn = 1) as after_dedup
from orders
""")
con.execute("delete from orders where rowid in (select max(rowid) from orders group by order_id having count(*) > 1)") before_dedup after_dedup
10 9Always state the tiebreak. “Keep the latest by updated_at” and “keep any one” are different
answers, and the interviewer is checking whether you noticed there was a choice.
10. Cohort retention
q("""
with first_order as (
select customer_id, min(ordered_at) as cohort_day from orders group by 1
)
select f.cohort_day,
count(distinct f.customer_id) as cohort_size,
count(distinct o.customer_id) filter (
o.ordered_at between f.cohort_day + 1 and f.cohort_day + 7) as active_week_1
from first_order f
left join orders o using (customer_id)
group by 1 order by 1
""") cohort_day cohort_size active_week_1
2026-01-04 2 2
2026-01-05 1 1
2026-01-12 1 0Cohort questions are always: define the cohort, then left join activity, then aggregate with a
filter on the offset. Getting the LEFT JOIN right matters — an inner join drops cohorts with
no retained users, which flatters the numbers.
11. Median and percentiles
q("""
select
round(avg(amount), 2) as mean,
round(median(amount), 2) as median,
round(quantile_cont(amount, 0.25), 2) as p25,
round(quantile_cont(amount, 0.90), 2) as p90
from orders where status = 'completed'
""") mean median p25 p90
26.02 25.50 15.75 56.60Standard SQL is PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount). Knowing there is no
portable MEDIAN() — and that the mean sits above it on skewed money data — is the point of
the question.
12. The query plan question
“This query is slow. What do you check?”
print(con.execute("""
explain analyze
select c.country, count(*) from orders o join customers c using (customer_id)
where o.ordered_at >= date '2026-01-05' group by 1
""").fetchall()[0][1][:600])┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│ HASH_GROUP_BY │
│ ──────────────────── │
│ Groups: country │
│ Result: 2 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ Conditions: customer_id│
└─────────────┬─────────────┘The answer is a checklist, not a guess: is the filter sargable (no function wrapping the column), does the join key have an index or matching partitioning, how many rows does the largest node emit versus consume, and is anything spilling to disk. Lesson 9 covers this round properly.
A checklist for the round
Before you say “done”, every time:
- Row counts — did the join drop or multiply rows?
- Grain — is the key unique, and did you say so?
- NULLs — in the join key, in the aggregate, in a
NOT IN? - Ties —
ROW_NUMBERversusRANK, and is theORDER BYdeterministic? - Missing rows — days with no data, customers with no orders?
- Reconcile — does the total match the source?
Saying two or three of those out loud is what the round is actually scoring.
Practice
1. Write top-2-per-group with a self-join, then with a window function.
self-join: 6 rows, O(n²), missed customer 4
window function: 7 rows, one passThe self-join also silently dropped customer 4, who has one order — HAVING count(*) <= 2
behaves differently at the boundary. Window functions are both faster and less error-prone.
2. Use NOT IN against a column containing NULL.
NOT IN: 0 rows
NOT EXISTS: 1 rowNOT IN with any NULL in the list can never return TRUE. Use NOT EXISTS or
LEFT JOIN ... IS NULL — and mention why, because this is a deliberate trap.
3. Compare a RANGE frame with a ROWS frame on tied dates.
amount range_default rows_frame
31.2 191.84 160.64
31.2 191.84 191.84RANGE treats tied rows as peers and gives them the same total. Write ROWS explicitly
unless peer grouping is what you want.
4. Compute day-over-day change without a date spine, then with one.
without spine: 2026-01-08 compares to 2026-01-06
with spine: 2026-01-08 compares to 2026-01-07 (0.00)LAG moves by row, not by day. Any “day over day” question with sparse data needs the spine
first.
Next: the Python round — data manipulation without pandas, and with it.