Lesson 3 of 10 · 40 min
Analyst-level interview questions
By the end of this lesson you can recognise and solve the seven patterns that dominate analyst interviews (latest record per entity, top-N per group, running totals, period-over-period growth, medians, conditional-aggregation pivots and self-join pairs) on schemas you have not seen before.
Analyst questions are rarely about a single clause. They combine a filter, an aggregation and a window function, and the interviewer watches how you structure the steps. Almost all of them are one of seven patterns. Learn to name the pattern in the first minute (“this is top-N per group”) and the rest is assembly.
| Pattern | Typical wording | Tool |
|---|---|---|
| Latest record per entity | “each customer's most recent …”, “current salary” | ROW_NUMBER() … = 1 or DISTINCT ON |
| Top-N per group | “top 3 products in each category” | RANK/DENSE_RANK/ROW_NUMBER with PARTITION BY |
| Running total | “cumulative”, “year to date” | SUM() OVER (ORDER BY …) |
| Period over period | “month-over-month growth”, “vs last year” | LAG() |
| Median / percentile | “typical”, “median”, “p90” | percentile_cont(…) WITHIN GROUP |
| Pivot | “one column per status / channel” | COUNT(*) FILTER (WHERE …) or SUM(CASE …) |
| Pairs | “products bought together”, “employees in the same team” | self-join with a.id < b.id |
This lesson, its 7 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons