Skip to content
Tutorials Center
My learning

Topics

Lesson 124 of 125 ยท Practice and Review

SQL Puzzles and Interview Patterns

Master the SQL patterns interviews love: top-N per group, running totals, consecutive-day streaks, missing-ID gaps and duplicate removal.

A handful of query shapes show up again and again in SQL interviews and in real analytics work. "Show the top three products in each category." "Give me a running total that restarts every month." "Find each user's longest streak of consecutive days." They look like different puzzles, but each one is a short recipe built from tools you already know, mostly window functions and CTEs.

This lesson collects five of these recipes. For each one you'll see the idea, a runnable solution, and the traps that make the obvious answer wrong. Learn the shapes, not the exact queries: once you recognize "this is an islands problem" or "this is a top-N-per-group problem", the solution writes itself.

Pattern 1: top N per group

LIMIT 3 gives the top three rows of the whole table. To get the top three per category, you need a number for each row within its group, and that is exactly what ROW_NUMBER() with PARTITION BY provides. Number the rows, then keep the ones numbered at most N.

You can't filter on a window function in the same SELECT (the WHERE clause runs before windows are computed), so the numbering goes in a subquery or CTE:

SQL The two priciest products in each category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Twelve rows come back, two per category: Audio's top is Bookshelf Speakers at 149 followed by Studio Headphones at 119, Outdoor's is the Ultralight Tent at 189, and so on. The p.id after the price is a tie-breaker. Without it, two products with the same price would be ordered arbitrarily, and the query could return different rows on different runs.

Ties: ROW_NUMBER, RANK or DENSE_RANK?

The three ranking functions behave differently when values tie, and the interviewer's question usually hides which one is wanted:

  • ROW_NUMBER() never ties: it breaks ties arbitrarily (or by your tie-breaker). Exactly N rows per group.
  • RANK() gives tied rows the same rank and then skips: 1, 2, 2, 4. "Top 2" can return more than 2 rows.
  • DENSE_RANK() gives tied rows the same rank without skipping: 1, 2, 2, 3. "Top 2 distinct values", ties included.
SQL The three ranking functions with tied scores

Press Run (or Ctrl + Enter) to see the result here.

Look at the blue team: Fay, Gus and Hal all have 35 points. RANK and DENSE_RANK give all three the rank 2, while ROW_NUMBER numbers them 2, 3 and 4, and only the player tie-breaker decides who is which. The red team shows the difference between the other two: Ava and Ben tie at 30 and both get rank 1, then Cai is rank 3 under RANK (a number is skipped) but rank 2 under DENSE_RANK, and Dee is 4 versus 3.

Ties are decided by the ORDER BY inside the OVER clause. If you add a tie-breaker such as player to the window of RANK, the rows no longer tie, so keep the tie-breaker only where you want distinct numbers.

To get "everyone in the top 2 places, including ties", use WHERE rank <= 2 with RANK() or DENSE_RANK(). To get "exactly 2 people per group", use ROW_NUMBER() with a tie-breaker. Say which one you chose and why.

Pattern 2: running totals, in several flavors

A running total is SUM(...) OVER (ORDER BY ...). Interviewers like to ask for variants, and each one differs in a single clause:

Variant Change
Running total over all rows SUM(x) OVER (ORDER BY d)
Restart for every group add PARTITION BY region
Restart every month PARTITION BY strftime('%Y-%m', d)
Moving average of the last 3 rows AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Percent of the grand total so far running sum divided by SUM(x) OVER ()

All five run in one query on a small sales table. The ORDER BY sold_on, region inside the window is again a tie-breaker, so two sales on the same day don't get the same running total (see Window Frames for why).

SQL Five running-total variants

Press Run (or Ctrl + Enter) to see the result here.

Check the last row: running total 910, the north region alone 420 and the south 490 (running_region is 490 for 2025-03-15), 200 for March, and the three-row moving average (60 + 90 + 110) / 3 = 86.7. The percentage reaches 100.0 on the last row, a handy sanity check for any running-percentage query.

Pattern 3: consecutive days (gaps and islands)

"Find the longest streak of consecutive days each user logged in" is the most famous SQL puzzle. Rows that belong together (a streak, or an island) are separated by missing days (gaps).

The trick: number each user's distinct days with ROW_NUMBER(), then subtract that number of days from the date. Inside one streak, the date goes up by 1 each row and the row number also goes up by 1, so the difference is constant. A gap makes the date jump ahead while the row number moves one step, so the difference changes. Rows with the same difference form one island.

flowchart LR
  A["Distinct days per user"] --> B["ROW_NUMBER() per user<br/>ordered by day"]
  B --> C["group key =<br/>day minus row number"]
  C --> D["GROUP BY user, key"]
  D --> E["MIN(day), MAX(day), COUNT(*)<br/>= one streak"]

Gaps and islands: subtracting the row number turns each run of consecutive days into one constant group key.

For user 1, the days 03-01, 03-02, 03-03 have row numbers 1, 2, 3, so day - rn is 02-28 for all of them. The next login is 03-05 with row number 4: 03-05 - 4 days is 03-01, a different key, so a new island starts.

SQL Streaks of consecutive login days

Press Run (or Ctrl + Enter) to see the result here.

User 1 has two streaks (03-01 to 03-03 with 3 days, and 03-05 to 03-06 with 2 days) and user 2 has a lone day and then a five-day streak from 03-04 to 03-08. Note the SELECT DISTINCT in the first CTE: user 1 logged in twice on 03-03, and without removing the duplicate the row numbers would drift and split a real streak in two. To get only each user's longest streak, wrap this query in another CTE and take MAX(days) per user (3 for user 1 and 5 for user 2).

A second way to build the island key avoids date arithmetic, so it works on any ordered column and any database. Use LAG to flag the start of a streak, then a running SUM of the flags:

SQL
WITH flagged AS (
  SELECT user_id, day,
         CASE WHEN julianday(day) - julianday(
                LAG(day) OVER (PARTITION BY user_id ORDER BY day)) = 1
              THEN 0 ELSE 1 END AS starts_streak
  FROM (SELECT DISTINCT user_id, day FROM logins)
)
SELECT user_id, day,
       SUM(starts_streak) OVER (PARTITION BY user_id ORDER BY day) AS streak_id
FROM flagged;

The first row of each user has no previous day (LAG returns NULL, the subtraction is NULL, the CASE falls to ELSE), so it also starts a streak. This "flag, then running sum" idea turns up in many puzzles, for example splitting a stream of events into sessions after 30 minutes of inactivity. See LAG and LEAD.

Pattern 4: finding gaps in a sequence

"Which ticket numbers are missing?" is the same idea. Look at each id and the next one: if the difference is more than 1, everything between them is missing.

Pattern 5: removing duplicates

"Keep only the most recent row per email" is a top-1-per-group problem. Number the rows within each email from newest to oldest, and delete everything numbered above 1. Both patterns run together below:

SQL Missing ids and duplicate removal

Press Run (or Ctrl + Enter) to see the result here.

The gap query reports ids 4 to 6 (3 missing) and ids 9 to 11 (3 missing). The id after the last row, 12, can't have a gap after it, and LEAD gives NULL there, which makes the WHERE false. After the delete, only rows 4 (ann's newest) and 5 (bob's duplicate with the higher id) remain. Bob's two rows tie on created_at, so the id DESC tie-breaker decides which one survives, and it makes the result predictable.

Always run the window query as a SELECT first and look at the rows marked for deletion, then convert it to a DELETE. Better still, do it inside a transaction you can roll back.

How to attack an unfamiliar puzzle

  1. Say the grain. What does one output row represent: a group, a user, a day?
  2. Ask about ties and duplicates before writing a line. "Top 3" could mean three rows or three values.
  3. Build it in layers with CTEs: clean data, number or flag the rows, then aggregate. Check each layer with a SELECT.
  4. Test on a tiny table that includes the awkward cases: ties, an empty group, a single row, a gap at the edge.
  5. Name the pattern out loud: top-N per group, running total, islands, gap detection, dedupe. Naming it shows you recognize the family.

For more practice, work through the SQL Practice Exercises and the SQL Interview Questions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can't you write WHERE ROW_NUMBER() OVER (...) <= 3 directly?

    WHERE is processed before window functions are computed. Putting the window function in a subquery or CTE makes its result an ordinary column that the outer WHERE can filter.
  2. 2. Three players tie for second place. Which function gives all three the rank 2 and the next player the rank 3?

    DENSE_RANK gives tied rows the same rank without skipping numbers, so the next player gets 3. RANK would also tie them at 2 but then jump to 5. ROW_NUMBER never ties.
  3. 3. In the gaps-and-islands trick, why is (day minus row number) constant inside a streak?

    Inside a run of consecutive days, each step moves the date by one day and the row number by one, so subtracting cancels out. A gap makes the date jump further than the row number, which changes the key and starts a new island.
  4. 4. What does the tie-breaker (for example ORDER BY created_at DESC, id DESC) accomplish in a de-duplication query?

    Without a unique ordering, rows that tie on the sort column are numbered in an unspecified order, so the query could keep different rows on different runs.

Summary

  • Top N per group: number rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY ...) in a subquery, then filter. Use RANK or DENSE_RANK when ties should share a place, and always add a tie-breaker for ROW_NUMBER.
  • Running totals are SUM() OVER (ORDER BY ...). PARTITION BY restarts them per group or month, ROWS BETWEEN makes a moving window, and dividing by SUM() OVER () gives a running percentage.
  • Consecutive streaks (islands): deduplicate, number the rows, subtract the number from the date, and group by the result. The LAG flag plus running SUM is the portable alternative.
  • Gaps in a sequence: compare each value with LEAD; a difference above 1 marks a gap.
  • Duplicates: ROW_NUMBER() ... WHERE rn > 1 identifies the rows to delete. Preview before you delete.
  • Name the pattern, state the grain and the tie rules, and test on a tiny table.

That's the last lesson of the SQL tutorial. Keep practicing with the SQL Practice Exercises and the SQL Interview Questions.