SQL patterns that interviews repeat

SQL rounds repeat the same questions in new words. "The best seller in each category." "The latest status of each order." "Remove the duplicate rows." "The median delivery fee." Many candidates know every keyword and still stop, because they do not see the shape of the question.

A weak answer tries a new idea for each question and runs out of time. A strong answer hears the shape ("top N per group"), picks the recipe, and then spends the time on the traps: ties, empty values and edges.

At SDE-2 level you should know the top N and latest-row recipes without help. At Senior level you should also do medians, the price on a date and running balances, and say how ties change the answer.

A pattern has three parts: how to recognise it from the words of the question, the recipe in steps, and the trap that gives a wrong answer without an error. Every recipe here uses only the tools of lessons 42 to 47. Most of them build a ranked or running column in a CTE (lesson 46), and then filter on it outside.

1. Top N per group, and the Nth per group

Recognise it: "the top 2 in each city", "the best seller per month", "the second order of each customer".

Recipe: (1) Make one row per thing you rank. (2) Rank it inside the group: ROW_NUMBER() OVER (PARTITION BY group ORDER BY score DESC, id). (3) In an outer query, keep rn <= N, or rn = N for the Nth.

Trap: ties. Pick the function from the tie rule that the question allows. ROW_NUMBER gives exactly N rows and breaks a tie by your tie-break column. RANK and DENSE_RANK can return more than N rows, because tied rows share a place. Ask the interviewer. Another trap: ORDER BY ... LIMIT 2 gives the top 2 of the whole result, not of each group. Also, rn <= 2 returns only one row for a city that has one restaurant. Say if that is what is wanted.

We want the top 2 restaurants in each city by money from delivered orders.

WITH sales AS (
  SELECT r.city, r.name, SUM(o.amount) AS money
  FROM orders AS o JOIN restaurants AS r ON r.id = o.restaurant_id
  WHERE o.status = 'delivered' GROUP BY r.id, r.city, r.name
),
ranked AS (
  SELECT city, name, money, ROW_NUMBER() OVER (PARTITION BY city ORDER BY money DESC, name) AS rn FROM sales
)
SELECT city, name, money, rn FROM ranked WHERE rn <= 2 ORDER BY city, rn;

city       name          money  rn
---------  ------------  -----  --
Bengaluru  Dosa Corner     920   1
Delhi      Spice Route    1480   1
Delhi      Roti Ghar      1400   2
Mumbai     Bombay Bites   1100   1
Mumbai     Thali Tales     680   2
Pune       Misal Mane     1050   1

For the Nth row, keep rn = N. The second order of each customer:

WITH numbered AS (
  SELECT id, customer_id, ordered_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, id) AS rn
  FROM orders WHERE customer_id IS NOT NULL
)
SELECT customer_id, id AS second_order, date(ordered_at) AS day FROM numbered WHERE rn = 2 ORDER BY customer_id;

customer_id  second_order  day
-----------  ------------  ----------
          1             4  2026-03-07
          2             7  2026-03-12
          3             9  2026-03-15
          4            11  2026-03-20
          5            12  2026-03-22
          6            22  2026-05-09
          7            23  2026-05-14

"And others". To show the top 3 and put all the rest into one row, rank first. Then rename every row with rn > 3 to Others and group again. Sort so that Others is last.

WITH per_item AS (
  SELECT item, SUM(qty * price) AS money FROM order_items GROUP BY item
),
ranked AS (
  SELECT item, money, ROW_NUMBER() OVER (ORDER BY money DESC, item) AS rn FROM per_item
)
SELECT CASE WHEN rn <= 3 THEN item ELSE 'Others' END AS item, SUM(money) AS money
FROM ranked GROUP BY CASE WHEN rn <= 3 THEN item ELSE 'Others' END
ORDER BY CASE WHEN rn <= 3 THEN 0 ELSE 1 END, money DESC;

item            money
--------------  -----
Butter Chicken   1280
Misal Pav        1010
Veg Thali        1000
Others           4970

2. The latest row of each group, and duplicates

Recognise it: "the current status", "the most recent price", "keep only the newest", "remove duplicates".

Recipe: Rank inside the group by the time, newest first, and keep rn = 1. For duplicates, number the rows of each key, oldest first. The rows with rn > 1 are the extra copies. Make this a plain SELECT first, and look at the rows it lists.

Trap: the sort column must break ties (use the id), or two rows with the same time can swap. Also decide which copy to keep: the first or the last.

We add two small tables for this lesson.

CREATE TABLE order_status_log (id INTEGER PRIMARY KEY, order_id INTEGER, status TEXT, changed_at TEXT);
INSERT INTO order_status_log VALUES
  (1, 1, 'placed', '2026-03-02 13:10:00'), (2, 1, 'cooking', '2026-03-02 13:20:00'), (3, 1, 'delivered', '2026-03-02 13:55:00'),
  (4, 2, 'placed', '2026-03-03 20:15:00'), (5, 2, 'cooking', '2026-03-03 20:25:00'),
  (6, 3, 'placed', '2026-03-05 19:40:00'), (7, 3, 'cancelled', '2026-03-05 19:45:00');
CREATE TABLE wishlist (id INTEGER PRIMARY KEY, customer_id INTEGER, item TEXT, saved_at TEXT);
INSERT INTO wishlist VALUES
  (1, 1, 'Butter Chicken', '2026-03-01 10:00:00'), (2, 1, 'Pani Puri', '2026-03-01 10:05:00'), (3, 1, 'Butter Chicken', '2026-03-02 09:00:00'),
  (4, 2, 'Vada Pav', '2026-03-02 11:00:00'), (5, 1, 'Butter Chicken', '2026-03-03 08:00:00'), (6, 2, 'Vada Pav', '2026-03-02 11:00:00');

WITH ranked AS (
  SELECT order_id, status, changed_at, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY changed_at DESC, id DESC) AS rn FROM order_status_log
)
SELECT order_id, status AS current_status FROM ranked WHERE rn = 1 ORDER BY order_id;

order_id  current_status
--------  --------------
       1  delivered
       2  cooking
       3  cancelled

WITH numbered AS (
  SELECT id, customer_id, item, ROW_NUMBER() OVER (PARTITION BY customer_id, item ORDER BY saved_at, id) AS rn FROM wishlist
)
SELECT id, customer_id, item FROM numbered WHERE rn > 1 ORDER BY id;

id  customer_id  item
--  -----------  --------------
 3            1  Butter Chicken
 5            1  Butter Chicken
 6            2  Vada Pav

Rows 3, 5 and 6 are the extra copies. The oldest copy of each pair stays. Row 6 has the same time as row 4, so id decides.

3. Median and percentiles

Recognise it: "median", "middle value", "90th percentile", "P90".

Recipe for the median: number the rows by the value, count all the rows with COUNT(*) OVER (), and keep the middle one or two. For n rows, the middle rows are number (n + 1) / 2 and (n + 2) / 2 with whole-number division. For an odd n they are the same row; for an even n they are the two in the middle. Then take their AVG. For a median per group, partition both functions.

WITH ranked AS (
  SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn, COUNT(*) OVER () AS n FROM orders WHERE status = 'delivered'
)
SELECT n, AVG(amount) AS median FROM ranked WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

 n  median
--  ------
20   340.0

Recipe for a percentile (nearest rank): the value of the smallest row whose CUME_DIST() is at least the percentage, for example 0.9. This is the nearest-rank rule: the value at position 0.9 x n, rounded up. Decimals can be off by a tiny amount, so if the cut is exact, round cd to 6 places first.

WITH ranked AS (
  SELECT amount, CUME_DIST() OVER (ORDER BY amount) AS cd FROM orders WHERE status = 'delivered'
)
SELECT MIN(amount) AS p90 FROM ranked WHERE cd >= 0.9;

p90
---
440

Trap: there are other definitions of a percentile that mix two values. Ask which one the question wants. An average of the middle two is the usual median.

4. The value on a date

Recognise it: "the price on the order date", "the rate in force", a table of changes with a "from" date.

Recipe: join each row to the rule row with the biggest from_date that is not after the date of the row. Two ways: a correlated subquery that picks the biggest date, or a period table built with LEAD and a range join (lesson 44). We add the delivery fee rules.

CREATE TABLE fee_rules (from_date TEXT, fee INTEGER);
INSERT INTO fee_rules VALUES ('2026-01-01', 30), ('2026-03-15', 40), ('2026-05-01', 50);

SELECT o.id, date(o.ordered_at) AS day,
  (SELECT f.fee FROM fee_rules AS f WHERE f.from_date <= date(o.ordered_at) ORDER BY f.from_date DESC LIMIT 1) AS fee
FROM orders AS o WHERE o.id IN (13, 14, 20, 21, 23) ORDER BY o.id;

id  day         fee
--  ----------  ---
13  2026-03-25   40
14  2026-03-31   40
20  2026-04-21   40
21  2026-05-03   50
23  2026-05-14   50

The second way gives each rule an end date, which is the start of the next rule. The newest rule has no end date.

WITH periods AS (
  SELECT from_date, LEAD(from_date) OVER (ORDER BY from_date) AS to_date, fee FROM fee_rules
)
SELECT o.id, date(o.ordered_at) AS day, p.fee
FROM orders AS o JOIN periods AS p ON date(o.ordered_at) >= p.from_date AND (p.to_date IS NULL OR date(o.ordered_at) < p.to_date)
WHERE o.id IN (13, 14, 20, 21, 23) ORDER BY o.id;

id  day         fee
--  ----------  ---
13  2026-03-25   40
14  2026-03-31   40
20  2026-04-21   40
21  2026-05-03   50
23  2026-05-14   50

The two ways give the same fees. If a date is before the first rule, the first way returns NULL:

SELECT (SELECT f.fee FROM fee_rules AS f WHERE f.from_date <= '2025-12-31' ORDER BY f.from_date DESC LIMIT 1) AS fee_before_the_first_rule;

fee_before_the_first_rule
-------------------------
NULL

Add an earlier rule, or use COALESCE, if that can happen.

Trap: <= against <. A rule that starts on the order day applies on that day. If you build a to_date, compare the date of the order and not its time. Otherwise an order at 18:00 on the last day is lost (lesson 42).

5. Running balance, and a share of the total

Recognise it: "the balance after each move", "the first time it goes below zero", "the customers who make 80 percent".

Recipe: a running sum with SUM(change) OVER (ORDER BY day, id). For the first time below zero, filter the CTE on balance < 0 and take the smallest day. For a share, divide a running sum by the total, SUM(x) OVER ().

CREATE TABLE wallet_moves (id INTEGER PRIMARY KEY, day TEXT, change INTEGER);
INSERT INTO wallet_moves VALUES (1, '2026-03-01', 500), (2, '2026-03-02', -200), (3, '2026-03-04', -250), (4, '2026-03-05', 100), (5, '2026-03-06', -300), (6, '2026-03-08', 400);

WITH balances AS (
  SELECT id, day, change, SUM(change) OVER (ORDER BY day, id) AS balance FROM wallet_moves
)
SELECT day, change, balance FROM balances WHERE balance < 0 ORDER BY day, id LIMIT 1;

day         change  balance
----------  ------  -------
2026-03-06    -300     -150

For "the customers who make 80 percent of the revenue", sort customers by money and take a running share. Keep every customer whose running share before them is under 80 percent. The customer who crosses the line stays in.

WITH money AS (
  SELECT customer_id, SUM(amount) AS money FROM orders WHERE status = 'delivered' AND customer_id IS NOT NULL GROUP BY customer_id
),
shares AS (
  SELECT customer_id, money, SUM(money) OVER (ORDER BY money DESC, customer_id) AS running, SUM(money) OVER () AS total FROM money
)
SELECT customer_id, money, ROUND(100.0 * running / total, 1) AS running_percent
FROM shares WHERE running - money < 0.8 * total ORDER BY running;

customer_id  money  running_percent
-----------  -----  ---------------
          1   2280             33.5
          3   1350             53.4
          2    840             65.7
          4    770             77.1
          5    680             87.1

Trap: keeping running <= 0.8 * total drops the customer who crosses 80 percent. The default frame also treats ties as peers (lesson 47), so add a tie-break.

6. Pairs that go together

Recognise it: "bought together", "often seen with", "pairs of".

Recipe: join the line table to itself on the same order, with a.item < b.item. This lists each pair once. Group the pairs and count the orders.

SELECT a.item AS item_a, b.item AS item_b, COUNT(*) AS orders
FROM order_items AS a JOIN order_items AS b ON a.order_id = b.order_id AND a.item < b.item
GROUP BY a.item, b.item HAVING COUNT(*) >= 2 ORDER BY orders DESC, item_a;

item_a          item_b            orders
--------------  ----------------  ------
Misal Pav       Sabudana Khichdi       3
Butter Chicken  Butter Naan            2
Filter Coffee   Masala Dosa            2
Idli Plate      Masala Dosa            2
Misal Pav       Pav Bhaji              2
Puran Poli      Veg Thali              2

Trap: with <> instead of <, each pair appears twice, once in each order. A pivot (rows to columns) needs no new recipe: it is one SUM(CASE ...) for each column, as in lesson 45.

In Postgres and MySQL

• Postgres and MySQL 8 have the same window functions. In MySQL, / gives a decimal, so write (n + 1) DIV 2 and (n + 2) DIV 2 in the median recipe. Otherwise an even n uses only one of the two middle rows, and the median is wrong with no error. The date functions also differ.
• Postgres has PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) for a median, and PERCENTILE_DISC for nearest rank. MySQL has no such function.
• Postgres has DISTINCT ON (key) to keep the first row of each key. MySQL and SQLite do not.
• Some systems (BigQuery, Snowflake) have QUALIFY rn = 1. Postgres, MySQL and SQLite do not, so use a CTE.

The question: "Show the top 2 restaurants of each city by money."

First try (wrong). Sort by money and take 2.

SELECT r.city, r.name, SUM(o.amount) AS money
FROM orders AS o JOIN restaurants AS r ON r.id = o.restaurant_id
WHERE o.status = 'delivered' GROUP BY r.id, r.city, r.name
ORDER BY money DESC LIMIT 2;

city   name         money
-----  -----------  -----
Delhi  Spice Route   1480
Delhi  Roti Ghar     1400

This returns the top 2 of the whole result, both in Delhi. Mumbai, Bengaluru and Pune are missing. LIMIT does not work per group.

Fix. Rank inside each city with PARTITION BY city and keep rn <= 2. The first query of this lesson does it. Delhi shows Spice Route and Roti Ghar, and Mumbai shows Bombay Bites and Thali Tales. Bengaluru and Pune have one restaurant each.

The tie. Now "the top restaurant of each city by number of delivered orders". In Delhi, Spice Route and Roti Ghar both have 3.

WITH counts AS (
  SELECT r.city, r.name, COUNT(*) AS orders FROM orders AS o JOIN restaurants AS r ON r.id = o.restaurant_id
  WHERE o.status = 'delivered' GROUP BY r.id, r.city, r.name
),
ranked AS (
  SELECT city, name, orders,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY orders DESC, name) AS row_no,
    RANK() OVER (PARTITION BY city ORDER BY orders DESC) AS rank_no
  FROM counts
)
SELECT city, name, orders, row_no, rank_no FROM ranked WHERE row_no = 1 OR rank_no = 1 ORDER BY city, row_no;

city       name          orders  row_no  rank_no
---------  ------------  ------  ------  -------
Bengaluru  Dosa Corner        3       1        1
Delhi      Roti Ghar          3       1        1
Delhi      Spice Route        3       2        1
Mumbai     Bombay Bites       4       1        1
Pune       Misal Mane         3       1        1

ROW_NUMBER returns one Delhi restaurant (the tie-break chose Roti Ghar by name). RANK returns both. This answers the predict box. Both are right for a different rule, so say the rule: "if two tie, do both show?"

Check by hand. Delhi has restaurants 1, 2 and 3. Delivered orders: restaurant 1 has orders 1, 10 and 16 (3 orders). Restaurant 2 has orders 4, 20 and 21 (3 orders). Restaurant 3 has orders 8 and 11 (2). The result agrees.

• It solves: the common questions with one short pattern each, and the same code works for any group.
• It costs: a window over the whole table must sort it. On a huge table this is slow without an index on the partition and order columns (lesson 36). And each recipe gives a wrong answer without an error if the tie rule is wrong.
• I would change it when: the data is very large and the answer is needed often. Then I keep the latest row in its own table, updated on each change, so that the app does not rank every time.
• Always say the tie rule, and what happens with fewer than N rows.

"LIMIT gives the top N for each group." It gives N rows in total. Use ROW_NUMBER() OVER (PARTITION BY ...) and keep rn <= N.

"ROW_NUMBER, RANK and DENSE_RANK are the same for top N." They are the same only when there are no ties. With ties they return different numbers of rows.

"The median is AVG." The average adds up all the values. The median is the middle value. A few very large orders move the average but not the median.

1. Which window function gives exactly N rows per group, even with ties? Which can give more?
2. The delivered amounts of five orders are 100, 200, 300, 400 and 500. What is the median, and what is the median if a sixth order of 600 is added? Which rows do you keep for n = 6?
3. A teammate keeps the "customers who make 80 percent of revenue" with WHERE running <= 0.8 * total. Why can this give a wrong answer, and what do you change?