Data Analyst Interview Prep Guide

This is the single most predictable analyst-screen shape: a per-group ranking plus a windowed total. Use DENSE_RANK() (or ROW_NUMBER() if ties should break arbitrarily) PARTITION BY category ORDER BY revenue DESC, and SUM(revenue) OVER (PARTITION BY category) for the share denominator. Filter the quarter in WHERE before windowing so you are not ranking rows you will discard. Say out loud which window function you picked and why — "I used DENSE_RANK so genuine ties both surface; ROW_NUMBER would silently drop one." Careery lists "second-highest salary in each department" as a canonical version of this prompt, and window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, rolling aggregates) are the advanced-SQL battleground for analyst roles specifically.

Do NOT open a query editor first — interviewers are scoring your decomposition, not your typing. Step 1: validate the metric itself (definition unchanged? logging deploy? a dashboard or pipeline bug, not a real drop?). Exponent’s own canonical prompt is "Sales dropped 25% last month. How would you investigate?" and the model answer starts with "is the number even real." Step 2: segment the drop — platform, geography, app version, new-vs-returning cohort, acquisition channel — to localize it. Step 3: line it up against a deploy log, feature-flag change, or marketing/seasonality calendar. Step 4: form one or two ranked hypotheses and state how you would confirm each. Close with a one-sentence narrative for the PM, not a list of queries you ran.

THIS is the round most strong SQL candidates fail — the product-sense / metric-definition question, and it is largely absent from the popular question-bank pages. Use a 4-layer frame: (1) one primary goal metric tied to the feature’s job-to-be-done (e.g., daily Stories viewers / DAU, not "total views" which a single power user inflates); (2) input metrics that move it (creation rate, reshares, time-to-first-Story); (3) guardrail metrics that must NOT regress (feed engagement, session length, report/block rate, latency); (4) the trade-off you are explicitly accepting (cannibalizing feed time may be fine if total app time rises). Naming a guardrail and a trade-off is the senior tell. A vanity metric with no guardrail is the junior tell.

Define the primary metric (conversion rate) and guardrails (revenue per user, page-load time, refund rate). Size the test from your baseline rate, the minimum detectable effect you actually care about, alpha (0.05), and power (0.80) — state those four inputs explicitly; "I would run it for two weeks" with no power calc is the fail. Then volunteer the threats, because that is the differentiator: peeking / early-stopping inflating false positives, multiple-comparison correction if you read several metrics, day-of-week and novelty/primacy effects forcing at least one full weekly cycle, seasonality, and cross-variant contamination (shared accounts, network effects). Trigger the experiment only for eligible users so dilution does not crush your power.

A trap that rewards skepticism over enthusiasm — Careery lists it verbatim. Interrogate the number before you celebrate it: Is it a real trend or one whale / one enterprise deal? Is the comparison clean (same number of business days, no calendar or billing-cycle artifact)? Is it a pull-forward that will dent next month? Is it a definition or pipeline change rather than real growth? Foreign-exchange or a one-off refund reversal? The signal here is the same instinct as the "metric dropped" prompt pointed the other way: a strong analyst is equally suspicious of good news and bad news.

Dataquest flags this as a core statistics question (alongside p-values, Type I/II errors, and mean vs median). Define cleanly: correlation is co-movement; causation means intervening on X changes Y. Then make it concrete with a confounder you actually hit — "users who used feature X retained better, but both were driven by tenure; when I controlled for account age the effect halved." Close with how you would establish causation when you cannot just eyeball it: a randomized A/B test first, and quasi-experimental methods (difference-in-differences, regression discontinuity, instrumental variables) when an experiment is not possible. Concrete confounder + named method beats a textbook definition.

Lead with mechanism, not method: figure out WHY it is missing (missing completely at random, missing at random conditional on other fields, or missing not at random) because that decides everything downstream. Only then choose: complete-case analysis if it is truly random and the sample stays large; an explicit missing-indicator if missingness itself is signal; median or model-based imputation if the column is needed and the pattern allows; or fixing the logging upstream if it is a collection bug. State the trade-off each choice imposes on validity — naive mean-imputation, for instance, shrinks variance and can manufacture a false signal. NULL handling is on Dataquest’s "appears in almost every interview" shortlist (JOINs, GROUP BY, subqueries, NULL handling).

Hiring managers ask for both because the second half is where the signal is. For the win, run the full arc: business question -> method -> insight -> how you delivered it -> the decision -> the measured outcome, quantified (revenue, cost, retention points). For the rejected one, avoid blaming stakeholders; show what you learned about framing or timing — Exponent’s behavioral set includes "Describe a time your analysis was wrong. What did you learn?" and rewards a specific lesson (their sample answer turns on confusing correlation for causation in a churn model). Owning a miss with a concrete behavior change reads more senior than three flawless wins.