The SQL barely changes between a junior and a senior analyst loop. What changes is how much of the problem you're handed. Junior rounds give you a spec and check the rows. Senior rounds hand you an ambiguous business question and watch you define the metric — the query is the easy half.
Six areas carry almost every analyst interview: joins (including the anti-join), aggregation with GROUP BY and HAVING, window functions, subqueries and CTEs, conditional logic with CASE, and date handling. Those are the six our own Interview Prep track targets, and they are not a coincidence — they are the areas where a business question maps onto a non-obvious SQL shape.
Now the part most prep material gets wrong. Indexes, execution plans, partitioning strategy, and schema design are data engineer questions. They show up in analyst loops rarely and usually as a "do you know this exists" aside, not as a graded exercise. Time spent memorising index types is time not spent on the top-N-per-group pattern you will almost certainly be asked to write.
The one exception worth knowing. If the role is titled "analytics engineer", or the job description mentions dbt, the loop drifts toward engineering: incremental models, testing, and yes, some performance. Read the posting, not the job title — "Data Analyst" at one company is "Analytics Engineer" at another.
This is the axis most prep lists ignore. Preparing for a senior round the way you'd prepare for a junior one is the most common way strong candidates underperform.
The spec is precise on purpose. "Return the name and total for every customer who spent more than the average." Success is a correct result set; the interviewer is checking mechanics.
Prepare by: volume on Easy and Medium until syntax stops costing you thought.
The question arrives deliberately underspecified. "Find our most engaged customers." There is no definition of engaged, and supplying one out loud — then defending it — is most of the score.
Prepare by: Hard challenges, and practising narration — say the plan aloud before typing.
You're expected to interrogate the ask. What will this number miss? What happens to it if the table has duplicates, or NULLs in the join key? What would you validate before anyone makes a decision on it?
Prepare by: Hard set, then re-solving with the question "how would this be wrong in production?"
The trap in senior rounds. Producing a fast, correct query in silence reads as junior. The interviewer cannot grade the thinking they cannot hear, and at 5+ years the thinking is the thing being graded. Narrate the definition, the assumption, and the caveat — even when the SQL is easy for you.
Not eight questions — eight shapes. Interview questions are these wearing different business clothes.
GROUP BY key HAVING COUNT(*) > 1 — then the follow-up: which copy would you keep, and why? Full walkthrough →
Best-selling product in each category. Needs ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) — a plain GROUP BY with MAX can't return the other columns of the winning row, and saying so scores points.
Customers who never ordered. LEFT JOIN … WHERE right.id IS NULL, or NOT EXISTS. Know why NOT IN with a NULL in the subquery returns nothing at all — this is a favourite trap.
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING). The frame clause is the part people forget, and the part that changes the answer.
LAG() for the previous value, then the percentage. Handle the first row's NULL explicitly instead of letting it become a division error. Worked example →
SUM(CASE WHEN … THEN 1 ELSE 0 END) to pivot rows into columns in one pass. Once you see it, half of "build me this report" questions collapse into it.
First-purchase month per customer, then activity relative to it. Two CTEs and a self-join. This is the archetypal 2–5 year question.
Not every warehouse gives you PERCENTILE_CONT. ROW_NUMBER from both ends and take the middle. Asked specifically to see whether you can build a primitive you're missing.
Ordered by the levels above. Every one runs in the browser against real sample data and is checked against the expected result — no self-marking.
Counting Across a JOIN
Where a fan-out silently inflates your count — the first thing to get right.
Find Duplicate Emails
Pattern 1. If you rehearse one query, rehearse this one.
Below Department Average
Correlated subquery — compare each row to its own group's aggregate.
Top-N Products per Category
Pattern 2, the one that separates candidates who've practised from those who haven't.
Month-over-Month Revenue Growth
Pattern 5. Chained CTEs plus LAG, with the first-row NULL to handle.
Customer Retention Cohort
Pattern 7 — the archetypal mid-level question, end to end.
Median Salary Without PERCENTILE
Pattern 8. Build the primitive your warehouse didn't give you.
Deduplicate Orders with ROW_NUMBER
The duplicate question at senior depth: now choose the survivor deliberately.
Or start the SQL Interview Prep track in the Coach — ~25 hours across 34 steps, and it skips what your skill radar shows you already have.
The eight patterns are universal. The framing isn't — a marketplace asks about supply and demand, a payments company asks about reconciliation.
Most analysts who fail a SQL round already knew the answer — they just couldn't produce it while someone watched. That's a practice problem, and it's fixable in a couple of weeks.
Start Free — No Signup ⚡250+ challenges · Adaptive Coach · Skill radar · Pro from $29/month or $199 one-time