15. Classic interview patterns¶
A small number of question shapes make up most SQL test questions. Once you recognise the shape, the query almost writes itself. This chapter collects them, each with a worked answer on the sample data and the wrong answer people usually give: the Nth highest value, duplicates, the latest row per key, streaks of consecutive days (gaps and islands), pivots, the median, "in every month", and "bought together".
In this chapter
You will learn the patterns:
- Nth highest, four ways, including the one that must return NULL when there is no Nth
- finding duplicates
- consecutive days and streaks (gaps and islands)
- pivot: rows into columns
- the median, in both dialects
- "every month" and other "for all" questions
- pairs that occur together (market basket)
You need: chapters 7 to 14. Each pattern names the chapter it builds on.
15.1 The Nth highest¶
"The second-highest salary." Four ways; the first is the one to reach for.
1. DENSE_RANK (chapter 13): rank the salary levels, keep level N.
WITH levels AS (
SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
WHERE salary IS NOT NULL
)
SELECT salary AS second_highest FROM levels WHERE dr = 2;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| second_highest |
|---|
| 140000 |
2. The largest below the largest (only for N = 2):
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Ran on PostgreSQL 16 and SQLite 3.53: same rows as the other version. 1 row.
| second_highest |
|---|
| 140000 |
3. DISTINCT with LIMIT and OFFSET: sort the different salaries, skip N − 1, take one.
SELECT DISTINCT salary AS second_highest
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Ran on PostgreSQL 16 and SQLite 3.53: same rows as the other version. 1 row.
| second_highest |
|---|
| 140000 |
4. Correlated count: a salary that exactly N − 1 different salaries are above.
SELECT DISTINCT e.salary AS second_highest
FROM employees e
WHERE (SELECT COUNT(DISTINCT x.salary) FROM employees x WHERE x.salary > e.salary) = 1;
Ran on PostgreSQL 16 and SQLite 3.53: same rows as the other version. 1 row.
| second_highest |
|---|
| 140000 |
The usual wrong answer: no DISTINCT
SELECT salary AS third_highest
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| third_highest | | --- | | 120000 |
The third-highest salary is 110,000 (after 180,000 and 140,000). Without DISTINCT the two people on 120,000 take places 3 and 4, and OFFSET 2 lands on one of them. "Third highest salary" means the third different value.
When there is no Nth. A well-known variant says: "if there is no second-highest salary, return NULL". The queries above return no row in that case, which is not the same thing. Wrap the query as a scalar subquery: a scalar subquery with no row gives NULL (chapter 10). Here, asking for the 10th-highest of the 9 salary levels:
SELECT (
SELECT DISTINCT salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 9
) AS tenth_highest;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| tenth_highest |
|---|
| NULL |
One row, holding NULL. The SELECT MAX(...) WHERE salary < (SELECT MAX ...) version (way 2) also returns NULL
rather than nothing, because an aggregate over no rows gives one row with NULL.
15.2 Duplicates¶
Finding them is a GROUP BY on the columns that should be unique, keeping groups with more than one row:
SELECT customer_id, login_date, COUNT(*) AS copies
FROM logins
GROUP BY customer_id, login_date
HAVING COUNT(*) > 1;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| customer_id | login_date | copies |
|---|---|---|
| 2 | 2026-03-02 | 2 |
To see every duplicated row with its other columns, count with a window instead, so the rows are kept:
WITH c AS (
SELECT customer_id, name, city,
COUNT(*) OVER (PARTITION BY name) AS same_name
FROM customers
)
SELECT customer_id, name, city FROM c WHERE same_name > 1 ORDER BY customer_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 2 rows.
| customer_id | name | city |
|---|---|---|
| 1 | Ana Silva | Toronto |
| 10 | Ana Silva | Lisbon |
Two customers called Ana Silva, in different cities: "duplicate" depends on which columns you compare. Removing duplicates (keep one, delete the rest) is in chapter 22.
15.3 Consecutive days: gaps and islands¶
"The longest run of consecutive login days for each customer." This is the gaps and islands problem: each run of consecutive days is an island, separated by gaps.
The trick: number each customer's login days 1, 2, 3, ... in date order, and subtract that number (as days) from the date. Within a run of consecutive days, the date goes up by one and the number goes up by one, so the difference stays the same. When there is a gap, the date jumps ahead and the difference changes. The difference labels the island.
First remove duplicate days, or a second login on the same day takes a number and breaks the run. Customer 2 logged in twice on 2 March.
WITH days AS (
SELECT DISTINCT customer_id, login_date FROM logins
),
numbered AS (
SELECT customer_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) AS INTEGER) AS island
FROM days
)
SELECT customer_id, MIN(login_date) AS run_start, MAX(login_date) AS run_end, COUNT(*) AS days
FROM numbered
GROUP BY customer_id, island
ORDER BY customer_id, run_start;
Ran on PostgreSQL 16. 7 rows.
| customer_id | run_start | run_end | days |
|---|---|---|---|
| 1 | 2026-03-01 | 2026-03-03 | 3 |
| 1 | 2026-03-05 | 2026-03-06 | 2 |
| 1 | 2026-03-10 | 2026-03-10 | 1 |
| 2 | 2026-03-01 | 2026-03-02 | 2 |
| 2 | 2026-03-04 | 2026-03-04 | 1 |
| 4 | 2026-03-03 | 2026-03-06 | 4 |
| 6 | 2026-03-08 | 2026-03-08 | 1 |
WITH days AS (
SELECT DISTINCT customer_id, login_date FROM logins
),
numbered AS (
SELECT customer_id, login_date,
date(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) || ' days') AS island
FROM days
)
SELECT customer_id, MIN(login_date) AS run_start, MAX(login_date) AS run_end, COUNT(*) AS days
FROM numbered
GROUP BY customer_id, island
ORDER BY customer_id, run_start;
Ran on SQLite 3.53: same rows as the other version. 7 rows.
| customer_id | run_start | run_end | days |
|---|---|---|---|
| 1 | 2026-03-01 | 2026-03-03 | 3 |
| 1 | 2026-03-05 | 2026-03-06 | 2 |
| 1 | 2026-03-10 | 2026-03-10 | 1 |
| 2 | 2026-03-01 | 2026-03-02 | 2 |
| 2 | 2026-03-04 | 2026-03-04 | 1 |
| 4 | 2026-03-03 | 2026-03-06 | 4 |
| 6 | 2026-03-08 | 2026-03-08 | 1 |
Customer 1 has three islands (1–3 March, 5–6 March, 10 March); customer 4 one island of four days. The longest
streak per customer is then one more GROUP BY (MAX(days)), or "customers with a streak of at least 3 days" a
HAVING.
The usual wrong answer: forgetting the duplicate
WITH numbered AS (
SELECT customer_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) AS INTEGER) AS island
FROM logins
WHERE customer_id = 2
)
SELECT customer_id, MIN(login_date) AS run_start, MAX(login_date) AS run_end, COUNT(*) AS days
FROM numbered
GROUP BY customer_id, island
ORDER BY run_start;
Ran on PostgreSQL 16. 2 rows.
| customer_id | run_start | run_end | days |
|---|---|---|---|
| 2 | 2026-03-01 | 2026-03-04 | 3 |
| 2 | 2026-03-02 | 2026-03-02 | 1 |
Customer 2 logged in on 1, 2 and 4 March: a run of 2 days, then 1 day. Without DISTINCT the two 2 March rows get numbers 2 and 3, so every key after them shifts by one. 1 March, the first 2 March and 4 March land in one "island" from 1 to 4 March, counted as 3 days, with the gap on 3 March swallowed; the second 2 March row makes an island of its own. Both answers are wrong.
The same trick works on whole numbers (ids, sequence numbers): id - ROW_NUMBER() labels runs of consecutive ids,
which finds gaps in a sequence: wherever one island ends and the next starts.
15.4 Pivot: rows into columns¶
"Revenue per month, with one column per category." The data has one row per (month, category); the answer wants months down the side and categories across the top. Put a CASE inside each SUM, one per output column:
SELECT SUBSTR(CAST(o.order_date AS TEXT), 1, 7) AS month,
SUM(CASE WHEN p.category = 'Home' THEN i.quantity * i.unit_price ELSE 0 END) AS home,
SUM(CASE WHEN p.category = 'Kitchen' THEN i.quantity * i.unit_price ELSE 0 END) AS kitchen,
SUM(CASE WHEN p.category = 'Office' THEN i.quantity * i.unit_price ELSE 0 END) AS office,
SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'shipped'
GROUP BY SUBSTR(CAST(o.order_date AS TEXT), 1, 7)
ORDER BY month;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 4 rows.
| month | home | kitchen | office | total |
|---|---|---|---|---|
| 2026-01 | 24.50 | 17.50 | 199.50 | 241.50 |
| 2026-02 | 98.00 | 65.00 | 64.50 | 277.50 |
| 2026-03 | 49.00 | 58.25 | 215.25 | 322.50 |
| 2026-04 | 24.50 | 0 | 139.00 | 163.50 |
The ELSE 0 makes an empty cell 0; leave it out and an empty cell is NULL. The total includes the gift card,
which has no category column of its own, so the three columns don't add up to it in February: check your columns
against the total to catch rows that fall through. SQL Server and Oracle also have a PIVOT keyword; PostgreSQL and
SQLite don't, and the CASE form works everywhere, so learn that.
15.5 The median¶
PostgreSQL has an ordered-set aggregate for it; SQLite (as normally built) doesn't, so it needs window functions.
SELECT department,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
ORDER BY department;
Ran on PostgreSQL 16. 4 rows.
| department | median_salary |
|---|---|
| Engineering | 120000 |
| Management | 180000 |
| Sales | 70000 |
| Support | 63500 |
WITH r AS (
SELECT department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn,
COUNT(*) OVER (PARTITION BY department) AS n
FROM employees
WHERE salary IS NOT NULL
)
SELECT department, AVG(salary) AS median_salary
FROM r
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY department
ORDER BY department;
Ran on PostgreSQL 16 and SQLite 3.53: same rows as the other version. 4 rows.
| department | median_salary |
|---|---|
| Engineering | 120000.000000000000 |
| Management | 180000.000000000000 |
| Sales | 70000.000000000000 |
| Support | 63500.000000000000 |
The window version keeps the middle row (odd count) or the middle two (even count) and averages them. With whole
number division, (n + 1) / 2 and (n + 2) / 2 are the same row when n is odd and the two middle rows when n is
even. Ties in salary don't matter here: tied rows have the same value. PERCENTILE_CONT interpolates in the same way;
PERCENTILE_DISC(0.5) would return an actual value from the data instead (the lower middle one).
15.6 "In every ..."¶
"Customers who placed a shipped order in every month from January to April." Count the different months per customer and compare with the number of months:
SELECT customer_id
FROM orders
WHERE status = 'shipped'
AND order_date >= '2026-01-01' AND order_date < '2026-05-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT SUBSTR(CAST(order_date AS TEXT), 1, 7)) = 4;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| customer_id |
|---|
| 1 |
COUNT(DISTINCT month) is essential: Ana Silva has two January orders, and COUNT(*) = 4 would be true for someone
with four orders in one month. The double NOT EXISTS form of "for all" is in chapter 9 (question 9.6).
15.7 Bought together¶
"Which pairs of products appear in the same order most often?" A self join of order_items on order_id, with
a.product_id < b.product_id to count each pair once:
SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) AS orders_together
FROM order_items a
JOIN order_items b
ON b.order_id = a.order_id
AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
HAVING COUNT(*) >= 2
ORDER BY orders_together DESC, product_a, product_b;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| product_a | product_b | orders_together |
|---|---|---|
| 2 | 3 | 2 |
| 2 | 4 | 2 |
| 4 | 5 | 2 |
Three pairs were bought together twice: the notebook (2) with the fountain pen (3), in orders 105 and 117; the
notebook with the coffee mug (4), in 103 and 117; and the coffee mug with the tea kettle (5), in 109 and 115. Join to
products for names if the task wants them.
15.8 Recognising the pattern¶
| The question says | Pattern | Chapter |
|---|---|---|
| "the Nth highest", "the second largest" | DENSE_RANK = N, or DISTINCT + OFFSET | 13, 15.1 |
| "top N per ...", "the best in each ..." | RANK / ROW_NUMBER in a CTE, filter outside | 13 |
| "the latest / first ... for each ..." | ROW_NUMBER ... ORDER BY date DESC, id DESC = 1 | 13 |
| "duplicates", "appears more than once" | GROUP BY ... HAVING COUNT(*) > 1 | 15.2 |
| "consecutive", "streak", "in a row" | DISTINCT, then date − ROW_NUMBER | 15.3 |
| "compared with the previous", "change", "growth" | LAG | 14 |
| "so far", "cumulative", "to date" | SUM OVER (ORDER BY ... ROWS ...) | 14 |
| "one column per ..." | SUM(CASE ...) per column | 15.4 |
| "never", "without any", "no ..." | NOT EXISTS | 9 |
| "every", "all of" | COUNT(DISTINCT) = total, or double NOT EXISTS | 15.6, 9 |
| "percentage of", "share of" | 100.0 * x / SUM(x) OVER () | 13 |
| "including those with none", "show zero" | LEFT JOIN + COALESCE, or a calendar grid | 8, 16 |
Practice¶
15.1 The third-highest product price (a price level, ties counted once).
Answer
SELECT DISTINCT price AS third_highest
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 2;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| third_highest | | --- | | 25.00 |
The levels are 149.00, 32.00, 25.00, 24.50, ... The tea kettle and the monitor stand share 32.00, so the third level is 25.00.
15.2 For each customer who logged in, their longest streak of consecutive login days. (Write it for PostgreSQL; the SQLite version swaps the date arithmetic as in 15.3.)
Answer
WITH days AS (SELECT DISTINCT customer_id, login_date FROM logins),
numbered AS (
SELECT customer_id,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY login_date) AS INTEGER) AS island
FROM days
),
runs AS (
SELECT customer_id, island, COUNT(*) AS days FROM numbered GROUP BY customer_id, island
)
SELECT customer_id, MAX(days) AS longest_streak
FROM runs
GROUP BY customer_id
ORDER BY longest_streak DESC, customer_id;
Ran on PostgreSQL 16. 4 rows.
| customer_id | longest_streak |
|---|---|
| 4 | 4 |
| 1 | 3 |
| 2 | 2 |
| 6 | 1 |
15.3 Pivot: for each department, the number of employees hired in each of 2023, 2024 and 2025, as three columns.
Answer
SELECT department,
SUM(CASE WHEN SUBSTR(CAST(hire_date AS TEXT), 1, 4) = '2023' THEN 1 ELSE 0 END) AS hired_2023,
SUM(CASE WHEN SUBSTR(CAST(hire_date AS TEXT), 1, 4) = '2024' THEN 1 ELSE 0 END) AS hired_2024,
SUM(CASE WHEN SUBSTR(CAST(hire_date AS TEXT), 1, 4) = '2025' THEN 1 ELSE 0 END) AS hired_2025
FROM employees
GROUP BY department
ORDER BY department;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 4 rows.
| department | hired_2023 | hired_2024 | hired_2025 |
|---|---|---|---|
| Engineering | 0 | 1 | 0 |
| Management | 0 | 0 | 0 |
| Sales | 2 | 0 | 1 |
| Support | 0 | 1 | 0 |
15.4 Order ids are meant to run from 101 with no gaps. Check it: list any missing ids between the smallest and largest. (There are none in the sample; prove it.)
Answer
Gaps are found where the next id is more than one above this one:
WITH n AS (
SELECT order_id, LEAD(order_id) OVER (ORDER BY order_id) AS next_id
FROM orders
)
SELECT order_id + 1 AS gap_starts, next_id - 1 AS gap_ends
FROM n
WHERE next_id - order_id > 1;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 0 rows.
| gap_starts | gap_ends |
|---|---|
| (no rows) | |
No rows: no gaps. To be sure the check works, run it on a set with a hole, such as orders without order 110
(WHERE order_id <> 110 in the CTE); it reports 110 to 110.
15.5 The median order value (all orders, by revenue per order). Use the portable window method.
Answer
WITH v AS (
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items GROUP BY order_id
),
r AS (
SELECT total,
ROW_NUMBER() OVER (ORDER BY total, order_id) AS rn,
COUNT(*) OVER () AS n
FROM v
)
SELECT AVG(total) AS median_order_value
FROM r
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| median_order_value | | --- | | 36.7500000000000000 |
20 orders, so the median is the average of the 10th and 11th values in order.
15.6 Customers who ordered (any status) in two consecutive calendar months at least once.
Answer
Turn each month into a number (year * 12 + month) so that consecutive months differ by exactly 1, then
compare each customer's months with LAG:
WITH m AS (
SELECT DISTINCT customer_id,
CAST(SUBSTR(CAST(order_date AS TEXT), 1, 4) AS INTEGER) * 12
+ CAST(SUBSTR(CAST(order_date AS TEXT), 6, 2) AS INTEGER) AS month_no
FROM orders
WHERE customer_id IS NOT NULL
),
l AS (
SELECT customer_id, month_no,
LAG(month_no) OVER (PARTITION BY customer_id ORDER BY month_no) AS prev_month_no
FROM m
)
SELECT DISTINCT customer_id
FROM l
WHERE month_no - prev_month_no = 1
ORDER BY customer_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| customer_id | | --- | | 1 | | 2 | | 6 |
Next: 16. Dates and time series.