Data Analyst Interview Prep Guide

Use window functions: ROW_NUMBER() or RANK() partitioned by region, with SUM() OVER for calculating regional totals. Filter the date range in the WHERE clause before windowing. Discuss the choice between ROW_NUMBER (exactly 5) and RANK (handles ties). Mention indexing considerations for this query on a large dataset and how you would test correctness.

Start by validating the metric definition and measurement. Segment the drop by platform, geography, user cohort, and feature area. Check for external factors: holidays, competitor launches, or app store review changes. Look for data pipeline issues that might affect measurement. Correlate with deployments, feature flag changes, and marketing campaign schedules. Present your findings as a structured narrative, not a list of queries you ran.

Define the primary metric (conversion rate) and guardrail metrics (revenue per user, page load time). Calculate sample size based on desired minimum detectable effect, significance level, and statistical power. Discuss how you handle multiple comparisons, novelty effects, and day-of-week seasonality in duration planning. Mention practical considerations like triggering the experiment only for eligible users and avoiding contamination between variants.

Walk through the business question, your analysis methodology, key insights you discovered, how you communicated findings to stakeholders, the decision that was made, and the measurable outcome. Quantify the impact in business terms: revenue, cost savings, user growth, or efficiency gains. Show the complete arc from question to impact, not just the analysis itself.

Explain that correlation measures the statistical relationship between variables while causation implies one variable directly influences another. Provide a real example where a correlation was misleading: perhaps two metrics both correlated with a confounding variable. Discuss how you establish causation: controlled experiments, instrumental variables, regression discontinuity, or difference-in-differences analysis when experiments are not possible.

Define the retention metrics: D1, D7, D30 retention by cohort. Include segmentation by acquisition channel, user type, and geography. Add context with rolling averages and year-over-year comparisons. Discuss design principles: lead with the most actionable metric, avoid chart junk, provide drill-down capability, and include annotations for significant events. Mention the data pipeline requirements and refresh cadence.

First understand why the data is missing: is it missing completely at random, missing at random conditional on other variables, or not missing at random. Options include: complete case analysis if the missing data is truly random and the sample remains large enough, imputation (mean, median, or multiple imputation) if appropriate, creating a missing indicator variable, or addressing the data collection issue upstream. Discuss the tradeoffs and how your choice affects the validity of downstream analysis.

Focus on your communication strategy: leading with the business insight rather than the methodology, using visualizations that tell a story, avoiding statistical jargon, providing clear recommendations with confidence intervals, and adapting your presentation to the audience decision-making framework. Show that effective data communication is a skill you have developed intentionally.