← SQL roadmap

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Expert Interview Patterns” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.