[Intermediate] — what gets asked, what a strong answer covers, and the detail that separates memorised from understood. 🔥 marks the ones asked constantly. The analyst round is half SQL screen, half case study.
The SQL screen — expect it, live
🔥 Q: Get the top 3 products by sales in each category.
A strong answer covers: a window function — ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) in a subquery/CTE, then filter rn <= 3.
The detail worth adding: knowing why GROUP BY can’t do this (it collapses rows; you need per-row ranking that keeps the rows), and RANK() vs ROW_NUMBER() for ties → window functions. This exact question, or a close variant, is the most common analyst SQL screen.
🔥 Q: Compute month-over-month revenue growth.
A strong answer covers: aggregate to monthly revenue (CTE), then LAG(revenue) OVER (ORDER BY month) and the percentage change. Building it as chained CTEs, not nested subqueries.
The detail worth adding: the integer-division trap (100.0 *), and handling the first month’s NULL prev-value.
Q: Your query says £2M revenue, finance says £2.4M. What’s wrong?
A strong answer covers: almost certainly a join fan-out (a one-to-many join multiplying rows — check with COUNT(*) vs COUNT(DISTINCT)), a filter excluding rows finance includes, or a definition mismatch (“completed” orders vs all orders, test data in or out).
The detail worth adding: the instinct to stop and reconcile against a known source before trusting any analysis → sanity-check totals. Naming fan-out as the #1 analytical SQL bug shows real experience.
The diagnostic case — the heart of the round
🔥 Q: Revenue dropped 20% last week. Walk me through how you’d investigate.
A strong answer covers the method, not a guess:
- Is it real? Check against normal variation and seasonality — is last week actually low, or is it just a holiday/weekend effect? → seasonality
- Decompose — revenue = users × conversion × order value. Which factor moved? → the metric tree
- Segment the moved factor — by device, geography, channel — to localise it
- Correlate with events — a release, a price change, an outage, a campaign ending
- Form and verify a hypothesis
The detail worth adding, and it’s what they’re testing: structure over intuition. A weak candidate starts guessing causes; a strong one runs the search systematically and says “associated with” until they’ve verified. Mentioning that most such drops turn out to be seasonality or a deploy shows you’ve done this for real → diagnostic analysis.
Q: A/B test — variant B has higher conversion. Ship it?
A strong answer covers: not yet — is the difference statistically significant, and is the sample big enough? Check for pitfalls: sample-ratio mismatch, peeking (stopping early when it looked good), novelty effects, and whether a guardrail metric (retention, revenue) got worse while conversion improved → guardrails.
The detail worth adding: effect size and confidence interval, not just a p-value; and Goodhart — optimising conversion alone can hurt what you actually care about → A/B testing.
Metrics and product
🔥 Q: How would you measure the success of [feature / product]?
A strong answer covers: start from the decision/goal, pick an actionable metric (a rate, not a vanity count), pair a leading indicator with the lagging goal, and name a guardrail so you don’t win one metric while losing another. Define it precisely.
The detail worth adding: distinguishing the North Star (customer value) from revenue, and flagging the vanity trap (“total users only goes up”) → metrics and KPIs. This question is really “do you think in decisions or in numbers?”
Q: Define “active user.”
A strong answer covers: there’s no universal answer — it depends on the product — but it must be precise and singular: active how recently (DAU/WAU/MAU?), doing what (any action vs a meaningful one?). The point is that an ambiguous definition means every team reports a different number → the semantic layer.
Q: What’s a cohort analysis, and why use it?
A strong answer covers: grouping users by when they started and tracking each group over time — because it separates growth from retention (you can grow by acquisition while every cohort churns). The retention curve flattening is the product-market-fit signal → cohorts.
Q: LTV:CAC — what is it and why does it matter?
A strong answer covers: lifetime value over acquisition cost — the ratio that decides if the business model works. Below 1, you lose money on every customer and growth makes it worse. A retention improvement raises LTV and the ratio → unit economics.
Communication and judgement
🔥 Q: How do you present a finding to a non-technical executive?
A strong answer covers: lead with the answer (bottom line up front), state the “so what” and its size, then support it — tailored to what they need (the decision and magnitude, not your process). One chart, one message, message in the title.
The detail worth adding: honesty about uncertainty, not misleading with a truncated axis, and offering a recommendation rather than a neutral data dump → communicating analysis. This is often the question that most separates candidates, because it reveals whether they understand the deliverable is a decision.
Q: You found the data doesn’t support what your stakeholder hoped. What do you do?
A strong answer covers: present the honest finding, clearly and tactfully, with the evidence. Analyst integrity is presenting what the data says, not what someone wanted — and credibility (the analyst’s real currency) is destroyed by one confident wrong answer.
What this round tests
- Live SQL fluency — window functions especially, and the instinct to sanity-check
- Structured diagnosis — a method for “why did it change,” not guessing
- Product/metric sense — thinking in decisions and actionable metrics, not vanity numbers
- Communication — leading with the answer, honest, tied to an action
Related
- the data analysis course · projects
- databases interview — deeper SQL · ai-ml interview — the stats/DS side
- Interview Prep Index
Software engineering
Infrastructure & security
Computer science
Hardware & engineering