Interview Problems: Hard

Hard SQL problems combine two or three techniques: top N per group, medians, running totals, gaps and islands, filtered rates, pivots and recursive hierarchies.

Overview

Hard SQL problems rarely need an exotic feature. They need two or three ordinary techniques in the right order: rank, then filter; number the rows, then group; join, filter, then aggregate. They come up in senior backend, data engineering and analytics interviews.

The way through is to build the query in steps, each step a named CTE (a WITH query) that you can run and check on its own.

the top N rows in each group — window rank per group, then filter
a median or percentile — percentile_cont, or row numbers from both ends
a running or moving total — SUM() OVER (ORDER BY ...) with the right frame
streaks or consecutive rows — gaps and islands: value minus row number
a rate with exclusions — join to filter, then AVG(CASE ...)
rows turned into columns — conditional aggregation, or FILTER
a tree of any depth — WITH RECURSIVE

The problems below are written in our own words. Each block creates its own tiny tables and runs in PostgreSQL.

Top three salaries in each department

Return, for each department, every employee whose salary is among the three highest distinct salaries in that department.

The words 'distinct salaries' pick the ranking function. The three functions differ only on ties.

Ann — 120 — 1 — 1 — 1
Bo — 110 — 2 — 2 — 2
Cal — 110 — 3 — 2 — 2
Dee — 100 — 4 — 4 — 3
Eli — 90 — 5 — 5 — 4

ROW_NUMBER — breaks the tie arbitrarily (Bo and Cal could swap 2 and 3) — exactly N rows per group
RANK — same rank, then skips numbers — top N people, ties included
DENSE_RANK — same rank, no gaps — top N distinct values

Window functions run after WHERE, so you cannot filter on the rank in the same query. Rank in a CTE, then filter outside it.

Eng — Ann — 120
Eng — Bo — 110
Eng — Cal — 110
Eng — Dee — 100
Ops — Fay — 80
Ops — Gus — 70

Eng returns four people, because Bo and Cal share the second-highest salary.

Median salary per department

Return the median salary of each department. With an even number of people, the median is the mean of the two middle salaries.

PostgreSQL has no median() function, but it has the ordered-set aggregate percentile_cont, which interpolates between the two middle values.

Eng — 110
Ops — 75

Interviewers often ask for a version without that function. Number the rows in each department and keep the middle one or two. With n rows, the middle positions are (n + 1) / 2 and (n + 2) / 2 in integer division: both are 3 when n = 5, and 1 and 2 when n = 2.

Eng — 110.0
Ops — 75.0

Running totals and the frame trap

A running total is SUM(x) OVER (ORDER BY t). A moving total adds a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

1 — 10 — 10 — 10
2 — 20 — 30 — 30
3 — 30 — 60 — 60
4 — 40 — 100 — 90

The trap is the default frame. With an ORDER BY and no frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every peer: every row with the same ORDER BY value. When the ordering column has ties, all tied rows get the same total.

1 — 10 — 10 — 10
2 — 5 — 22 — 15
2 — 7 — 22 — 22
3 — 1 — 23 — 23

Use ROWS, with a tie-breaker in the ORDER BY, when each row needs its own total.

Gaps and islands: login streaks

Given a log of user logins (a user can log in several times a day), return each streak of three or more consecutive days, with its first and last day.

An island is a run of consecutive values. Number each user's distinct days in order. Inside a run, the day and the row number both go up by one, so day minus row number stays the same. That constant labels the island.

2026-05-01 — 1 — 2026-04-30
2026-05-02 — 2 — 2026-04-30
2026-05-03 — 3 — 2026-04-30
2026-05-05 — 4 — 2026-05-01

1 — 2026-05-01 — 2026-05-03 — 3

The DISTINCT step matters. Without it, the second login on May 2 gets its own row number and breaks the island in two. The same trick finds runs of consecutive ids, such as three or more rows in a row that meet a condition: filter first, then subtract the row number from the id.

A rate with exclusions: ride cancellations

Given rides (rider, driver, day, status) and a table that marks some accounts as banned, return the cancellation rate for each day, counting only rides where neither the rider nor the driver is banned. Round to two decimals.

2026-06-01 — 0.50
2026-06-02 — 0.00

Ride 2 is dropped because its rider is banned, so June 1 has one cancellation in two rides. Write 1.0, not 1, when you compute a rate as SUM divided by COUNT: in PostgreSQL an integer divided by an integer truncates, so 1 / 2 is 0.

Pivot rows into columns

Given revenue per product per quarter, return one row per product with a column for each quarter.

ink — 3 — NULL — 4
pens — 5 — 7 — 0

PostgreSQL supports FILTER; SUM(CASE WHEN quarter = 'Q1' THEN revenue END) does the same in every database. A sum over no rows is NULL, so wrap it in COALESCE when the answer needs 0. The columns must be known when you write the query; a pivot over values you do not know in advance needs dynamic SQL.

Walk a hierarchy with WITH RECURSIVE

Given an org chart where each person names their boss, return everyone with their depth below the CEO.

0 — CEO
1 — CFO
1 — CTO
2 — Dev lead
3 — Dev

The depth < 20 guard stops the query if bad data forms a cycle, where two people are each other's boss. PostgreSQL also has a CYCLE clause that detects a repeated row and stops that path.

What you will be able to do

Choose ROW_NUMBER, RANK or DENSE_RANK from the wording of a top-N question.
Compute a median with and without a percentile function.
Explain the default window frame and when to switch to ROWS.
Find streaks with the gaps-and-islands trick, including duplicate days.
Build a multi-step query as CTEs you can test one at a time.

When to use it

Senior backend, data engineering and analytics interviews
Ranking inside groups and then filtering on the rank
Finding streaks or runs of consecutive values in time-series data
Turning rows into a column-per-category report
Medians, percentiles, running and moving totals
Walking a parent-child table of any depth

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio