Topic 29 of 52 · Expert
Expert Interview Patterns
Gaps & islands, top-N per group, retention and median.
Concept
Most hard SQL interview questions reuse a handful of patterns. Recognising them is half the answer.
Top-N per group → window rank. Gaps & islands → date minus row_number. Retention/cohorts → first activity date per user joined back to activity. Running metrics → window SUM/AVG with frames.
Median without a MEDIAN function → ROW_NUMBER and COUNT over the partition, then pick the middle row(s).
Code (SQL · MySQL 8)
-- Median salary per department
WITH r AS (
SELECT dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) rn,
COUNT(*) OVER (PARTITION BY dept_id) cnt
FROM employees
)
SELECT dept_id, AVG(salary) AS median
FROM r
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY dept_id;Remember
- Say the pattern name out loud in interviews.
- Clarify ties, NULLs and time zones before writing SQL.
- Check edge cases: empty groups, duplicates.
Interview questions
Day-1 retention rate?
Find each user's first day, LEFT JOIN activity on user and first_day + 1, then divide matched users by total users.
Find gaps in an ID sequence.
Use LEAD(id) OVER (ORDER BY id) and keep rows where next_id - id > 1; the gap is id+1 to next_id-1.
Lesson test
Take an 8-question test on “Expert Interview Patterns” — concepts, output prediction, bug spotting and interview questions.