🎯Top SQL problem patterns

A small menu of relational drills

Interview loops recycle the same primitives: aggregates with filters, top-K per group, deduplication, gaps and islands, and self-join range overlaps. Narrate grain, NULLs, and ties before you write SQL, seniors care about trade-offs and tests, not the shortest clever line.

🧰Pattern → tools

Name the shape, then pick the operator.

Pattern	Typical tools
Top-K per group	ROW_NUMBER, DISTINCT ON, LATERAL
Conditional aggregation	FILTER (WHERE …), CASE
Pairs / overlap	Self-join, half-open range predicates

SQL

Example: employees earning above department average

SELECT e.employee_id, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) d USING (department_id)
WHERE e.salary > d.avg_salary;

✓Illustrative result set

employee_id	salary	avg_salary
12	118000	92000
44	142000	101000

One-liner theater

Clever SQL with no discussion of complexity or duplicates.
Ignoring ties in ranking questions or NULL salaries in aggregates.
Assuming DISTINCT repairs a bad join.

API-shaped answers

State keys, uniqueness, and tie rules before coding.
Sketch the schema; mention when indexes change asymptotics.
Propose tests: empty set, one row, duplicates, NULL edge bands.

Plans

Be ready to read cardinality and join type, interviewers often follow up with “what if this table explodes in size?” not only “does it run.”

TL;DR

Interview SQL drills usually test join + aggregate grain, NULL semantics, and window vs GROUP BY placement, state the row shape before and after each clause. Practice narrating complexity (time/memory) and safe rewrites (EXISTS vs IN, sargable predicates). Correct answers tie business keys to constraints and explain plans under skew, not only the first query that returns rows.