Case Study: The SQL Interview Gauntlet

Ask ten data engineers, analytics engineers, or data scientists what the SQL portion of their last interview loop actually contained, and a strange thing happens: the surface details differ (a funnel here, a cohort table there, a "duplicate rows" prompt somewhere else) but the underlying shapes repeat with remarkable consistency. There are only a handful of genuinely distinct problem patterns in interview SQL — first-occurrence-per-entity, retention-over-time, gap-based-grouping, keep-the-best-duplicate, running-aggregate, rank-within-group, period-over-period-comparison — and almost every "hard SQL question" you'll ever be asked is one of these patterns wearing a different business costume. A funnel question and a "first purchase per customer" question are the same MIN(CASE WHEN ...) pattern. A cohort retention question and a "days since last order" question both start from the same self-join-against-signup-date shape. Session gap-detection and "find consecutive account-active days" are the same LAG + running-sum trick.

This case study is a gauntlet through the seven patterns that come up most often, each posed the way an interviewer would actually pose it — a table, a business question, and an expectation that you write real SQL on a whiteboard or in a shared editor, not recite a memorized answer. What separates a candidate who's seen this exact question before from one who can derive it under pressure is the same thing sql-mental-model-and-query-execution spends an entire subject building: knowing the logical order a query actually executes in, so you can reason forward from "what does the engine have available at each step" instead of backward from "what query shape have I memorized for this word problem." Every solution below leans on window functions — advanced-sql-window-functions-and-ctes is the deep dive on ROW_NUMBER, RANK, DENSE_RANK, frame clauses, and LAG/LEAD if any of the syntax below is unfamiliar; this case study assumes that vocabulary and spends its time on when and why to reach for each tool, not on introducing the tools themselves.

One more framing point worth saying out loud in an interview: almost every problem below has a naive solution built on self-joins or correlated subqueries, and a window-function solution that is both more readable and dramatically cheaper to execute. Proposing the naive version first and then explaining why you'd replace it is often a stronger signal than jumping straight to the optimal query with no explanation — it shows you understand the cost model, not just the syntax. sql-query-optimization-and-indexing covers the execution-plan side of that cost model in depth; here, the point is narrower: recognize the shape, then reach for the cheap version on purpose.