SQL · Tier H

The 12 coding-interview patterns in SQL.

TL;DR

SQL interview rounds are a distinct lane. The patterns are small (window functions, CTEs, joins, aggregation), but mastery of a handful of idioms is often the whole game.

SQL strengths

  • Window functions (ROW_NUMBER, RANK, LAG, LEAD) solve 'top per group' and 'delta' questions in one query.
  • Common Table Expressions (CTEs) let you stage complex queries readably — interviewers reward readability heavily.
  • Self-joins handle pair-matching and hierarchical problems cleanly.

Watch-outs

  • `NULL` propagates — `COUNT(*)` and `COUNT(column)` differ on nulls.
  • `INNER JOIN` silently drops rows — use `LEFT JOIN` when the left side must be preserved.
  • Dialect differences (Postgres vs MySQL) on date functions and string functions — confirm the interviewer's dialect.

Reference idiom

WITH ranked AS (
  SELECT user_id, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
  FROM purchases
)
SELECT user_id, amount FROM ranked WHERE rn <= 3;

Signature patterns in SQL

Prefix & SuffixPrecompute cumulative arrays so range queries answer in O(1).Sliding WindowMaintain a contiguous range and slide its boundaries to avoid recomputation.Two PointersTwo cursors moving independently over a sorted or monotone structure.Dynamic ProgrammingBreak an overlapping-subproblem problem into a recurrence and cache results.

Frequently asked questions

Is SQL a good interview language?
SQL interview rounds are a distinct lane. The patterns are small (window functions, CTEs, joins, aggregation), but mastery of a handful of idioms is often the whole game.
What SQL patterns come up most in coding interviews?
The signature patterns in SQL interviews: Prefix & Suffix, Sliding Window, Two Pointers, Dynamic Programming.
What are common SQL interview pitfalls?
`NULL` propagates — `COUNT(*)` and `COUNT(column)` differ on nulls. `INNER JOIN` silently drops rows — use `LEFT JOIN` when the left side must be preserved. Dialect differences (Postgres vs MySQL) on date functions and string functions — confirm the interviewer's dialect.
Can I switch from Python to SQL mid-loop?
Most companies let you pick any language you're fluent in. Switching mid-loop usually hurts — pick the language you're fastest in and stick with it unless the company mandates otherwise.

Run the free diagnostic.

Ten-minute patterns quiz. No card. Personalized loop starts on the other side.

Start the diagnostic