The SQL patterns behind almost every interview question

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.