Data Analyst Interview Questions
The data analyst interview covers SQL, statistics and experimentation, product metrics, BI, and the open-ended analytics case.
Interview questions & answers
Write a query for revenue per customer, including customers with no orders. What are the gotchas?
LEFT JOIN customers to orders so zero-order customers survive. Aggregate the right-table key: COUNT(o.order_id) and COALESCE(SUM(o.amount),0) so no-match rows show 0, not 1 or NULL. Keep any right-table filter in the ON clause, not WHERE, or the LEFT JOIN silently becomes INNER. GROUP BY the customer key; order by revenue.
Return the most recent order per customer. How?
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in a CTE. Filter rn = 1 in the outer query — you can't filter a window function in the same WHERE. Alternative: correlated subquery on MAX(order_date), or QUALIFY rn = 1 where supported (Snowflake/BigQuery/DuckDB) — but the window-CTE version is the portable answer.
Explain the difference between WHERE and HAVING, and the logical order of a SELECT.
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That order explains why a SELECT alias isn't visible in WHERE, and why aggregates need HAVING.
A stakeholder says 'the p-value is 0.03, so there's a 3% chance the result is random.' Correct them.
A p-value is P(data this extreme given the null is true) — not the probability the result is random or the null is true. p = 0.03 means: if there were truly no effect, we'd see a difference this big only ~3% of the time. Report the confidence interval too, so they see direction and magnitude, and separate statistical from business significance.
Design an A/B test for a new checkout button. Walk through it.
One primary metric (checkout conversion) and a hypothesis stated before starting. Power calculation: minimum detectable effect, alpha 0.05, power 0.8 → required sample size and duration. Randomize per user; verify balance with an A/A / SRM check; run full weekly cycles with a pre-set stop date. Analyze once at the end; check guardrails (latency, refunds); decide on both significance and business impact.
Your A/B test 'won' but you checked it every day and stopped when it hit significance. What's wrong?
This is peeking / optional stopping — it inflates the 5% false-positive rate several-fold (20%+ in simulations of daily peeking). The fix is to pre-compute sample size and analyze once, or use a sequential test designed for continuous monitoring. Also verify no SRM and that the effect is large enough to matter, not just significant.
How would you define and measure 'active user' for a product?
State the definition explicitly: which action counts (any event vs a core action), over what window (DAU/WAU/MAU, 28-day), de-duplicated per user. Tie it to delivered value — prefer a core action (created a note, made a booking) over a passive login. Pair it with guardrails and cohort retention so a rising count isn't hiding churn.
Daily signups dropped 25% week-over-week. How do you investigate?
Clarify & quantify: exact metric, magnitude, window; step change or gradual? Rule out data issues first: logging/tracking change, pipeline failure, duplicates — most sudden drops are instrumentation. Internal vs external: our release/pricing/UI change vs seasonality, holiday, competitor, outage. Segment by platform, geo, channel, new-vs-returning to localize; decompose the rate into numerator vs denominator; then hypothesize and validate.
How would you measure the success of a newly launched feature?
Clarify the goal and what success means (adoption? retention? revenue?) and the timeframe. Measure across adoption, engagement, retention, and guardrails — not one number. Check for cannibalization of a more valuable behavior. For causation, prefer an A/B test; if fully rolled out, use matched cohorts / pre-post and caveat selection bias.
Estimate how many rides a ride-share app does per day in a mid-size city. Walk your reasoning.
State assumptions: city population, % who use ride-share, rides per active user per week. Build a top-down or bottom-up estimate (e.g. 1M people x 10% users x ~2 rides/week / 7 days). Sanity-check the number against supply (drivers x rides/driver/day) and note peaks (commute, weekend). Give a range and say which assumption you'd validate first with data.
A colleague made a bar chart with a y-axis starting at 90. What's your feedback?
A truncated y-axis exaggerates small differences and misleads — bar length no longer maps to value. Start bar charts at zero; if a zoomed view is genuinely needed, use a line chart or clearly label the break. General principle: the eye reads length and position accurately, so honest baselines matter most for bars.
After merging two DataFrames your revenue total doubled. Diagnose and fix.
Likely a many-to-many merge: the join key wasn't unique on one side, multiplying rows and inflating the sum. Check key uniqueness (df.key.duplicated().sum()); pass validate='many_to_one' so pandas raises instead of silently duplicating. Fix the key (dedup or aggregate to one row per key) before merging, then re-sum to confirm.
Design a star schema for a food-delivery app. Talk me through it.
Declare the grain first: one row per order line item in fact_order_items (measures: item price, quantity, fees, tip share). Dimensions: dim_customer, dim_restaurant, dim_courier, dim_date, dim_promo — the filter/group-by context. Handle change over time with SCD Type 2 (e.g. a restaurant's commission tier) so facts join to the state at order time. Note additivity: revenue sums anywhere; a semi-additive balance needs a separate snapshot fact.
Write SQL for monthly cohort retention: the share of each signup cohort active in later months.
Cohort each user: MIN(DATE_TRUNC('month', first_event)) as cohort_month in a CTE. Join activity back on user_id; compute months since cohort from cohort_month to activity month. Retention = COUNT(DISTINCT user_id) per (cohort_month, month_number) divided by the cohort's month-0 size. Pivot to the retention triangle with conditional aggregation if a matrix layout is requested.
Your dashboard query on a billion-row events table takes minutes. Speed it up.
Read the plan (EXPLAIN) first: full scan? exploding join? Confirm before changing anything. Scan less: select only needed columns (columnar engines), filter on the partition/cluster key, keep filters sargable — no functions wrapped around the filtered column. Check join grain — a fan-out join multiplies rows before aggregation. Pre-aggregate: an incremental daily rollup or materialized view turns a billion-row scan into thousands.
This round allows an AI assistant for SQL. How do you use it without getting burned?
Use it as a fast draft, then verify like a reviewer: join keys and grain (fan-out?), NULL handling, filter placement (the LEFT JOIN → WHERE trap). Sanity-check magnitudes: row counts at each step, totals against a known baseline. Narrate the verification out loud — that is what's being graded. Know the patterns cold anyway: you cannot review what you could not have written.