The eight SQL patterns that cover most analytics interviews
After sitting on both sides of a few hundred of these, the question bank is smaller than it looks. Eight shapes cover the overwhelming majority, and each one announces itself in the wording of the prompt.
By Lin S., founder and coach at Open Loop
There is a particular kind of candidate who is genuinely good at SQL and still fails SQL screens. They write correct queries at work every day, they have shipped models that other teams depend on, and then they sit down in a forty minute round and go quiet for four minutes while they think, and the interviewer writes down slow, needed hints.
Two things are going on. The first is that interview SQL is a small, weirdly specific genre and it rewards recognizing the genre. The second is that you are being scored on your narration as much as your syntax, which is not a skill anyone practices at their desk with nobody watching.
The prompt almost always tells you which pattern it wants. Learning to hear it is worth more than another fifty practice problems.
Below are the eight patterns I see over and over, each with the phrasing that signals it. Nothing here is exotic. The value is in the mapping, so that the first thirty seconds of the round are recognition rather than exploration.
| Pattern | The tell in the prompt | What you reach for |
|---|---|---|
| Top N per group | the top 3 for each, best per, highest in every | ROW_NUMBER in a subquery, filtered outside |
| Running total or moving average | cumulative, to date, rolling 7 day | SUM or AVG with a window frame |
| Period over period | compared to the previous, month over month growth | LAG or LEAD |
| Gaps and islands | consecutive days, streak, back to back | Date minus ROW_NUMBER as a grouping key |
| Funnel or conditional aggregation | “what percent of users who did X then did Y” | COUNT(DISTINCT CASE WHEN ...) |
| Cohort retention | day 7 retention, by signup month | First-seen CTE joined back to activity |
| Set membership | bought both, did A but never B | GROUP BY with HAVING, or NOT EXISTS |
| Latest record per key | current status, most recent, as of | ROW_NUMBER ordered descending, take row 1 |
1. Top N per group
The single most common window function question. The only trap is that you cannot filter on a window function in the same SELECT where you define it, because window functions evaluate after WHERE. You need a subquery, a CTE, or QUALIFY if your warehouse has it.
Top 3 products by revenue within each category
SELECT category, product, revenue
FROM (
SELECT
category,
product,
SUM(amount) AS revenue,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY SUM(amount) DESC
) AS rn
FROM sales
GROUP BY category, product
)
WHERE rn <= 3
ORDER BY category, revenue DESC;
Say the ranking-function choice out loud, because it is a free point. ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after a tie, DENSE_RANK does not. If two products tie for third, do you want three rows or four? Asking that question is a stronger signal than the query itself.
2. Running totals and moving averages
Same function family, different frame. The frame clause is the part people fumble, and it is worth memorizing exactly one of each so you are not reasoning it out under pressure.
Daily, cumulative, and a 7 day trailing average
WITH daily AS (
SELECT order_date, SUM(amount) AS revenue
FROM orders
GROUP BY order_date
)
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS trailing_7d
FROM daily
ORDER BY order_date;
3. Period over period
Month over month growth
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS mth, SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT
mth,
revenue,
LAG(revenue) OVER (ORDER BY mth) AS prev_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
/ NULLIF(LAG(revenue) OVER (ORDER BY mth), 0)
, 1) AS pct_change
FROM m
ORDER BY mth;
Two details that get noticed. 100.0 rather than 100, because integer division silently returns zero in Postgres and Redshift and will hand you a column of zeroes. And NULLIF on the denominator, because a zero month is not hypothetical when you slice by segment.
4. Gaps and islands
This is the one people either know or do not, and it looks like magic until you have seen it once. The insight: subtract a row number from a date, and any run of consecutive dates collapses to the same constant. That constant becomes your grouping key.
Longest streak of consecutive active days per user
WITH days AS (
SELECT DISTINCT user_id, activity_date
FROM events
),
keyed AS (
SELECT
user_id,
activity_date,
-- consecutive dates all produce the same streak_key
activity_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY activity_date
) * INTERVAL '1 day' AS streak_key
FROM days
)
SELECT
user_id,
COUNT(*) AS streak_length,
MIN(activity_date) AS started,
MAX(activity_date) AS ended
FROM keyed
GROUP BY user_id, streak_key
ORDER BY streak_length DESC;
The DISTINCT in the first CTE is load bearing. Two events on the same day would each get their own row number and the arithmetic falls apart. Point at it while you write it, because interviewers are specifically watching whether you noticed.
5. Funnels and conditional aggregation
The workhorse of product analytics interviews. One pass over the events table, one CASE per step, and you never need the self joins that candidates reach for first.
Checkout funnel in a single scan
SELECT
COUNT(DISTINCT session_id) AS sessions,
COUNT(DISTINCT CASE WHEN step = 'view_item' THEN session_id END) AS viewed,
COUNT(DISTINCT CASE WHEN step = 'add_cart' THEN session_id END) AS carted,
COUNT(DISTINCT CASE WHEN step = 'purchase' THEN session_id END) AS purchased,
ROUND(
100.0 * COUNT(DISTINCT CASE WHEN step = 'purchase' THEN session_id END)
/ NULLIF(COUNT(DISTINCT session_id), 0)
, 2) AS session_cvr
FROM funnel_events
WHERE event_at >= CURRENT_DATE - INTERVAL '7 days';
Then volunteer the caveat, because it separates people who have built a funnel from people who have read about one: this counts anybody who hit a step, in any order, at any time. If the question means users who did the steps in sequence, you need event ordering per session, and the query gets meaningfully harder. Naming that distinction unprompted is worth more than the query.
6. Cohort retention
Weekly retention by signup cohort
WITH first_seen AS (
SELECT user_id, MIN(DATE_TRUNC('week', activity_date)) AS cohort_week
FROM events
GROUP BY user_id
),
sizes AS (
SELECT cohort_week, COUNT(*) AS cohort_size
FROM first_seen
GROUP BY cohort_week
),
active AS (
SELECT DISTINCT user_id, DATE_TRUNC('week', activity_date) AS active_week
FROM events
)
SELECT
f.cohort_week,
DATEDIFF('week', f.cohort_week, a.active_week) AS weeks_out,
COUNT(DISTINCT a.user_id) AS retained,
ROUND(100.0 * COUNT(DISTINCT a.user_id) / MAX(s.cohort_size), 1) AS pct
FROM first_seen f
JOIN active a ON a.user_id = f.user_id
JOIN sizes s ON s.cohort_week = f.cohort_week
GROUP BY f.cohort_week, weeks_out
ORDER BY f.cohort_week, weeks_out;
Longer than the others and that is fine. Build it as named CTEs and say what each one holds before you write it. A reviewer reading four clearly named CTEs believes you have done this before; the same logic crammed into nested subqueries reads as luck even when it is correct.
7. Set membership
“Users who bought both A and B” invites a self join, and a self join is the wrong instinct because it does not generalize past two items. The aggregate version scales to any number and is shorter.
Both, and one but not the other
-- Bought both A and B, generalizes to any list
SELECT user_id
FROM purchases
WHERE sku IN ('A', 'B')
GROUP BY user_id
HAVING COUNT(DISTINCT sku) = 2;
-- Bought A but never B
SELECT DISTINCT p.user_id
FROM purchases p
WHERE p.sku = 'A'
AND NOT EXISTS (
SELECT 1 FROM purchases q
WHERE q.user_id = p.user_id AND q.sku = 'B'
);
8. Latest record per key
Current status from a history table
-- Portable version
SELECT user_id, status, updated_at
FROM (
SELECT
user_id, status, updated_at,
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY updated_at DESC
) AS rn
FROM user_status_history
)
WHERE rn = 1;
-- Snowflake and BigQuery, same thing with less ceremony
SELECT user_id, status, updated_at
FROM user_status_history
QUALIFY ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY updated_at DESC
) = 1;
Ask what happens on ties. If two rows share the same updated_at, you need a deterministic tiebreaker or your query returns different answers on different runs, which is the kind of bug that takes a week to find in production. One sentence, and it reads as someone who has debugged a real pipeline.
The traps worth memorizing
- A filter on the right side of a `LEFT JOIN`. Putting
WHERE b.status = 'x'after a left join turns it into an inner join and drops your unmatched rows. If the condition belongs to the join, put it inON. - Join fanout. If the right table has multiple matching rows, your
SUMis now inflated and your row count grew. Reaching forDISTINCTto make the numbers look right hides the bug rather than fixing it. Aggregate first, then join. COUNT(*)versusCOUNT(col).** The second one skips NULLs. This is occasionally what you want and usually an accident.- Integer division.
COUNT(a) / COUNT(*)returns 0 in several engines. Multiply by1.0or cast. - `DATE_TRUNC` and timezones. Warehouse UTC versus a business day in Pacific will move revenue across day boundaries and make your daily numbers disagree with the finance team's forever.
- `HAVING` versus `QUALIFY`.
HAVINGfilters groups,QUALIFYfilters window results. Neither one filters rows before aggregation, which isWHERE, and mixing them up is the most common logical error I see.
How to talk while you type
Here is what actually costs people this round. Not syntax. Silence. Forty minutes of an interviewer watching a cursor blink, then a correct query at minute thirty-eight and a scorecard that says struggled.
Say the plan before the first keyword. Ten seconds: “I need revenue per product per category, then rank inside category, then filter to the top three, so that is an aggregate in a CTE and a window function over it.” Now the interviewer knows you have it, and everything after is just typing. If your plan was wrong they will tell you at second eleven instead of minute thirty.
When something looks wrong, debug out loud and structurally. “That returned 4 million rows and the base table has 200 thousand, so I have a fanout on the join. Let me check the grain of the right table.” That sentence scores higher than a query that worked first time, because the interviewer's real question is what you do on the day the numbers are wrong, and they are always eventually wrong.
Last thing. Ask about the data before you write anything. Is user_id unique in this table? Can amount be negative for refunds? Are there soft-deleted rows? Two questions, fifteen seconds, and they change the query. Candidates who skip this write a beautiful query against a table they imagined, and the interviewer spends the debrief writing “did not validate assumptions”.