✏️ Explanatory Question
Level: Coding Round — A famous LeetCode problem; tests row-sequencing logic and comparing a row to its neighbours.
The Puzzle: Given a logs table with an ordered id and a num, find all numbers that appear at least three times consecutively (in three or more rows in a row).
logs| id | num |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
| ConsecutiveNums |
|---|
| 1 |
The number 1 appears in rows 1, 2, 3 (three in a row) → qualifies. The number 2 never appears 3 times consecutively.
The classic approach — three-way self-join: Join the table to itself twice to line up three consecutive rows (id, id+1, id+2), then check that all three have the same num. This directly expresses "three in a row."
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
JOIN logs l2 ON l1.id = l2.id - 1 -- l2 is the next row
JOIN logs l3 ON l1.id = l3.id - 2 -- l3 is the row after that
WHERE l1.num = l2.num
AND l2.num = l3.num; -- all three consecutive rows match Assumption to state: The self-join with id + 1 / id + 2 assumes ids are consecutive with no gaps. If ids can have gaps, use ROW_NUMBER() to create a gap-free sequence first, or use the window-function approach below which does not depend on id spacing.
-- Compare each row to the next two using LEAD
SELECT DISTINCT num AS ConsecutiveNums
FROM (
SELECT num,
LEAD(num, 1) OVER (ORDER BY id) AS next1,
LEAD(num, 2) OVER (ORDER BY id) AS next2
FROM logs
) t
WHERE num = next1 AND num = next2;-- Generalizes to "appears N+ times consecutively" using island grouping
SELECT num AS ConsecutiveNums, COUNT(*) AS streak
FROM (
SELECT num,
ROW_NUMBER() OVER (ORDER BY id)
- ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS grp
FROM logs
) t
GROUP BY num, grp
HAVING COUNT(*) >= 3; -- change 3 to any N
-- The (rn - rn_per_num) difference is constant within a consecutive run Why Solution 3 is the most powerful: The three-way self-join hard-codes "3." The gaps-and-islands trick (difference of two row numbers) groups any-length consecutive run into an island, so you can find "5 in a row" or "10 in a row" just by changing the HAVING number — far more flexible.
Interviewer follow-up: "Now return the longest consecutive streak for each number." → Use the islands query, but instead of filtering with HAVING, wrap it and take MAX(streak) per num: SELECT num, MAX(streak) FROM (islands query) GROUP BY num. The gaps-and-islands foundation makes this a one-line extension.