# The SQL patterns behind almost every interview question Source: https://www.techinterview.org/post/3233476262/sql-interview-question-patterns/ Updated: 2026-07-13 ยท techinterview.org Most SQL rounds recycle the same handful of shapes. The tables change, the column list changes, the interviewer swears this one is different, and the query you reach for is still one of a handful. Learn to read the shape out of the prompt and most of the work happens before you type a keyword. This holds across the whole spectrum, from a product-analytics screen at an early-stage startup to the SQL gauntlet at a data-heavy shop like Meta or a payments company. The schema is a distraction. What the interviewer is checking is whether you recognize that "the three most recent orders per customer" and "each department's top earner" are the same problem, and that "longest streak of active days" is a different problem that looks similar and trips people who guess. ## Read the pattern out of the prompt Before writing anything, translate the English into a pattern name. Certain phrases map almost one to one. "Per group" or "for each" plus "highest" or "latest" means top-N per group. "Running", "cumulative", or "rolling" means a windowed aggregate. "Compared to the previous", "since the last", or "day over day" means LAG. "Consecutive" or "streak" means gaps and islands. Say the mapping out loud in the room, because naming the pattern is half of what the interviewer wants to hear. The reference below is the version I would want taped to the monitor. Each row is a pattern, the way the question tends to be worded, and the construct that solves it. | SQL interview pattern | How the prompt usually sounds | Construct that solves it | | --- | --- | --- | | Top-N per group | "3 most recent orders per user", "highest-paid person in each department" | ROW_NUMBER() or RANK() OVER (PARTITION BY group ORDER BY x DESC), filtered in an outer query | | Nth highest / ranking with ties | "second highest salary", "top 3 salaries where ties count once" | DENSE_RANK() OVER (ORDER BY value DESC), keep rank = N | | Deduplication | "keep the latest row per email", "remove duplicate signups" | ROW_NUMBER() partitioned by the duplicate key, keep rn = 1 | | Running total / moving average | "cumulative revenue", "7-day rolling average" | SUM() or AVG() OVER (ORDER BY t ROWS BETWEEN ...) | | Row-over-row change | "day-over-day growth", "time since the previous order" | LAG() or LEAD() OVER (ORDER BY t) | | Gaps and islands | "longest login streak", "consecutive active days", "missing ID ranges" | value minus ROW_NUMBER() to tag runs, then GROUP BY the tag | | Conditional aggregation / pivot | "refunds and completed orders per user, side by side" | SUM(CASE WHEN condition THEN 1 or amount ELSE 0 END), GROUP BY | | Self-join | "employees earning more than their manager", "pairs of rows in one table" | join the table to itself on the relationship | ## Ranking, and the second-highest-salary trap "Find the second highest salary" is the most-asked SQL question in existence (LeetCode 176), and the naive answer is wrong more often than not. Taking `MAX(salary)` where salary is below `(SELECT MAX(salary))` works until two people tie for first, and now your "second highest" is actually the third distinct value. The clean answer picks the ranking function that matches how ties should behave. Three ranking functions, three behaviors. `ROW_NUMBER` gives every row a distinct number even on ties. `RANK` gives ties the same number and then skips (1, 1, 3). `DENSE_RANK` gives ties the same number and does not skip (1, 1, 2). "Second highest distinct salary" is `DENSE_RANK`. ``` SELECT name, salary FROM ( SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2; ``` The structural point carries over to every window-function question: you cannot filter on a window function in `WHERE`, because the window is computed after `WHERE` runs. Wrap it in a subquery or CTE and filter the alias in the outer query. Snowflake and BigQuery give you `QUALIFY` as a shortcut so you can skip the wrapper, which is worth saying if the interviewer is on one of those. ## Top-N per group without collapsing rows "The three most recent orders for each user." "Every department's top three earners" (LeetCode 185). The instinct to reach for `GROUP BY` fails here because grouping collapses the rows you are trying to keep. `ROW_NUMBER` partitioned by the group, ordered by whatever "top" means, tags each row with its rank inside the group. Filter to the number you want. ``` SELECT user_id, order_id, created_at FROM ( SELECT user_id, order_id, created_at, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC ) AS rn FROM orders ) t WHERE rn <= 3; ``` Swap `ROW_NUMBER` for `RANK` if the prompt says ties should all make the cut. The `PARTITION BY` is the group, the `ORDER BY` is the ranking rule, and the outer filter is N. That is the whole template, and it covers a startling share of medium-difficulty questions. ## Deduplication is top-N in disguise "Keep only the latest row per email." "There are duplicate signups; return one per user." Same machinery. Partition by whatever defines a duplicate, order so the row you want to keep sorts first, and keep `rn = 1`. ``` SELECT user_id, email, updated_at FROM ( SELECT user_id, email, updated_at, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY updated_at DESC ) AS rn FROM contacts ) t WHERE rn = 1; ``` If the question is a cleanup task, the same subquery feeds a `DELETE` that removes everything with `rn > 1`. Interviewers like this one because the wrong instinct, `SELECT DISTINCT`, silently fails the moment two "duplicate" rows differ in a column you do not care about. ## Running totals and row-over-row change Cumulative revenue, a seven-day moving average, month-to-date counts. These are aggregates with an `ORDER BY` inside the `OVER` clause and a frame that says how far back to reach. The frame clause is where people get vague, so be explicit: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` for a running total, `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` for a seven-row window. Row-over-row comparisons are the sibling. "Day-over-day change", "time between a user's consecutive purchases", "did revenue go up from yesterday" all want `LAG` for the previous row or `LEAD` for the next one. ``` SELECT order_date, revenue, SUM(revenue) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total, revenue - LAG(revenue) OVER (ORDER BY order_date) AS dod_change FROM daily_revenue; ``` One trap to name before they name it: if you leave off `ORDER BY` inside a windowed `SUM`, you get the total over the whole partition on every row, not a running total. Interviewers watch for whether you know the ordering is what makes it cumulative. ## Gaps and islands, or the date-minus-rownumber trick "Longest streak of consecutive login days." "Find the ranges of missing invoice numbers." "How many distinct sessions, where a session is activity with no 30-minute gap." This is the pattern people either know cold or reinvent badly under pressure. The trick is to subtract a row number from the sequence value. For consecutive dates, date minus row number stays constant across a run, so it becomes a group key. Then `GROUP BY` that key. ``` WITH tagged AS ( SELECT user_id, login_date, login_date - ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) * INTERVAL '1 day' AS streak_key FROM logins ) SELECT user_id, COUNT(*) AS streak_length, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end FROM tagged GROUP BY user_id, streak_key ORDER BY streak_length DESC; ``` That example is Postgres; SQL Server writes the offset as `DATEADD(day, -ROW_NUMBER() OVER (...), login_date)` and the idea is identical. Once the runs are tagged, longest streak is the biggest group, the current streak is the group containing today, and missing ranges fall out of the gaps between islands. The active-users problem (LeetCode 1454) and "consecutive available seats" are this pattern wearing costumes. ## Self-joins and conditional aggregation Two more shapes round out the set. A self-join handles "employees who earn more than their manager" (LeetCode 181) and any "compare a row to another row in the same table" question. Alias the table twice and join on the relationship. ``` SELECT e.name AS employee FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary; ``` Conditional aggregation, `SUM(CASE WHEN ... THEN ... ELSE 0 END)`, is how you pivot categories into columns and answer "count refunds and completed orders per user in one row." It shows up any time the prompt wants several counts or sums broken out by category, side by side. ``` SELECT user_id, COUNT(*) AS orders, SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds, SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS net_revenue FROM orders GROUP BY user_id; ``` ## Where candidates actually lose the round The pattern is usually the easy part. Points leak on the plumbing around it. `WHERE` filters rows before grouping and `HAVING` filters after, so a condition on `COUNT(*)` has to live in `HAVING`. A `NOT IN` against a subquery that contains a single `NULL` returns nothing at all, silently, which has ended more than one on-site. `COUNT(*)` counts rows while `COUNT(col)` skips nulls, and the interviewer chose that column for a reason. An inner join quietly drops the users with zero orders when the question specifically asked about them, so that one is a `LEFT JOIN`. The other thing they score is whether you narrate. Restate the question, call out your assumptions about ties and nulls, name the pattern before you write it, then talk through the query as you build. A candidate who says "this is a top-N per group, so ROW_NUMBER partitioned by department" and then writes exactly that has already outperformed the one who silently produces the same query, because the first one has shown they will recognize it again on a problem the interviewer has not shown them yet. Get these shapes into your fingers and the schema in front of you stops mattering.