Data Analyst Interview Prep
SQL, statistics, product-metrics and case questions from data analyst interviews, with model answers and a take-home approach.
Make Your Reasoning Easy to Follow
Strong analyst interviews are not trivia contests. Interviewers want to see that you can clarify the question, choose an appropriate method, validate the result, and explain what decision it supports. Before solving, state the grain, assumptions, time window and any ambiguity you need to resolve.
For a technical prompt, narrate the plan before typing. For an open-ended question, ask what the stakeholder needs to decide and what constraints matter. If you do not know a detail, say what you would verify rather than inventing a confident answer. Use the model responses below as patterns for concise reasoning, not scripts to memorize word for word.
- Restate the goal and ask one or two questions that could change the analysis.
- Declare the entity grain, population, dates, metric and assumptions.
- Work through a small example or validation before scaling the solution.
- Close with the finding, limitation and the next decision or test.
Show That You Can Query the Right Grain
After each model answer, the bullets identify evaluation signals interviewers may look for.
1. How would you find the second-highest salary in each department?
I would first clarify how ties should behave. If the requirement is the second distinct salary, I would rank distinct salary values within each department with DENSE_RANK, then select rank two. If the requirement instead means the second employee row, I would use ROW_NUMBER with a deterministic tie-breaker. That distinction changes the result when two people share the highest salary.
- distinguishes rank semantics and handles ties deliberately.
- partitions by department and defines behavior for departments with fewer than two salaries.
WITH ranked_salaries AS (
SELECT
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM (
SELECT DISTINCT department_id, salary
FROM employees
WHERE salary IS NOT NULL
) distinct_values
)
SELECT department_id, salary AS second_highest_salary
FROM ranked_salaries
WHERE salary_rank = 2;2. How do you find users who placed an order but never made a payment?
I would ask whether an order can have multiple payment attempts and whether refunded or failed payments count as a payment. Assuming a successful payment is represented by status settled, I would use NOT EXISTSso each qualifying order is tested without multiplying rows. I would then check the result against a small sample of order IDs and confirm the join key is stable.
- clarifies the business definition of a payment, including status and refunds.
- avoids accidental row multiplication and validates the anti-join result.
SELECT o.order_id, o.customer_id, o.created_at
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM payments p
WHERE p.order_id = o.order_id
AND p.status = 'settled'
);3. What is the difference between WHERE and HAVING?
WHERE filters input rows before grouping; HAVING filters grouped results after aggregation. For example, I would use WHERE created_at to restrict the date range before a customer count, then HAVING COUNT(*) > 1 to keep groups that meet an aggregate condition. Filtering early can reduce work, but the main point is to use each clause at the correct stage.
- understands the logical query stages and can give a correct example.
- avoids filtering an aggregate in WHERE when the database requires HAVING.
4. How would you calculate a seven-day rolling average of daily orders?
I would first create one row for every calendar date, including dates with zero orders, then aggregate daily orders. A window frame of six preceding rows through the current row gives seven dates only if the date series is complete. I would clarify whether the first six dates should be partial or blank and whether the business means calendar days or business days.
- catches missing-date bias and defines the window boundaries.
- separates a row-based frame from a true calendar-day window.
Explain Uncertainty Without Hiding Behind Jargon
5. What does a 95% confidence interval mean?
In a repeated-sampling interpretation, if we used the same valid procedure many times, about 95% of the intervals produced would cover the fixed population parameter. For the one interval we observed, I would report its bounds and method; I would not say there is a 95% probability that the fixed parameter is inside this already-computed interval.
- distinguishes coverage of a procedure from a probability statement about a fixed parameter.
- asks whether sampling and model assumptions support the interval.
6. A p-value is 0.03. What can you conclude?
If the null model and test assumptions hold, a p-value of 0.03 means results at least this incompatible with the null would occur with probability 0.03 under that model. It is not the probability that the null is true, nor evidence of business importance by itself. I would report the effect size, uncertainty, test design, and whether multiple tests or peeking affected the analysis.
- avoids interpreting the p-value as the probability the hypothesis is true.
- includes practical effect size, assumptions and analysis context.
7. How do you choose between a mean and a median?
I look at the distribution and the decision. The mean is useful when total magnitude matters and extreme values are meaningful; the median describes the middle observation and is less sensitive to a long tail. For delivery times, I would often show the median plus a high percentile because users care about typical and slow experiences. I would not remove outliers automatically without checking whether they are errors or real events.
- relates the summary to distribution shape and use case.
- treats outliers as a data question rather than an automatic deletion rule.
8. How would you compare an outcome between two groups?
I would identify the outcome type, unit of assignment, sampling process and whether the groups were randomized. For a randomized comparison, I would estimate the difference with an interval and check the experiment design and guardrails. For observational groups, I would describe the association and consider confounding; a simple significance test does not make the groups exchangeable or establish causation.
- starts with design and unit, not a reflexive test choice.
- distinguishes randomized evidence from observational comparisons.
import numpy as np
# Independent observations from two groups; replace with validated samples.
control = np.array([4.2, 5.1, 3.8, 6.0, 4.7])
treatment = np.array([5.0, 5.7, 4.9, 6.4, 5.3])
rng = np.random.default_rng(42)
differences = []
for _ in range(5000):
control_sample = rng.choice(control, size=len(control), replace=True)
treatment_sample = rng.choice(treatment, size=len(treatment), replace=True)
differences.append(treatment_sample.mean() - control_sample.mean())
estimate = treatment.mean() - control.mean()
low, high = np.quantile(differences, [0.025, 0.975])
print(f"Difference in means: {estimate:.2f}")
print(f"Bootstrap interval: [{low:.2f}, {high:.2f}]")This small example demonstrates resampling mechanics, not a substitute for checking experimental design, sample representativeness or assumptions. In an interview, explain which interval method you would use and why it fits the data.
Connect Metrics to User Behavior and Decisions
9. How would you define weekly active users?
I would ask what product behavior represents meaningful activity and which timezone defines a week. I would specify the eligible account types, user identity rule, treatment of bots and internal users, and whether the metric counts users or accounts. Then I would document the event and validate it against real journeys before comparing trends.
- treats an active-user metric as a product definition, not just a SQL count.
- considers identity, calendar boundaries, eligibility and instrumentation.
10. Conversion fell this week. What do you do first?
I would verify the metric and data freshness, compare like-for-like calendar periods, and inspect counts as well as rates. Then I would segment by platform, acquisition source and funnel step, checking sample sizes and recent releases or tracking changes. I would share what is confirmed, what is a hypothesis, and the next check before recommending a fix.
- checks instrumentation and denominator before explaining behavior.
- uses a structured diagnostic sequence and communicates uncertainty.
11. How would you measure a delivery-app checkout funnel?
I would define the eligible entry event and ordered steps, such as menu view, checkout start, payment success and order confirmation. I would choose user or session as the unit, define the conversion window, and ensure the events can be linked in order. I would report both step counts and conditional rates, then inspect device, payment method and delivery area segments without assuming a segment difference is causal.
- defines grain, order, time window and denominators.
- notices payment success and final confirmation may be distinct events.
12. A product manager asks for a north-star metric. How do you respond?
I would first ask what durable customer value the product promises and what behavior indicates that value was realized. I would propose a metric that is understandable, measurable and hard to inflate through low-quality activity, then pair it with guardrails for quality, cost or user harm. I would test whether teams can influence it and whether the definition is stable across cohorts.
- connects the metric to customer value and strategic decisions.
- anticipates gaming, trade-offs and guardrails.
Keep Reports Consistent and Reproducible
13. What is the difference between a fact and a dimension?
A fact records an event or measurement at a declared grain, such as one order line with quantity and net price. A dimension gives descriptive context, such as product category or customer segment, and filters facts through keys. I would state the grain before defining relationships because mixing order and order-line grain can duplicate totals.
- gives a concrete grain and correctly distinguishes measurements from descriptors.
- recognizes key uniqueness and double-count risk.
14. A dashboard total does not match finance. How do you investigate?
I would align the two metric definitions first: dates, timezone, status rules, refunds, tax, currency, and inclusion of test accounts. Then I would reconcile source records at a shared grain, inspect joins and relationship cardinality, compare refresh cutoffs, and trace the difference to a small set of rows. I would not adjust a report until the underlying rule is agreed and documented.
- treats reconciliation as definition plus lineage, not a chart-format issue.
- uses a traceable row-level method and communicates the resolution.
15. Why use a measure instead of a calculated column in a BI model?
A measure is evaluated for the current filter context, so it can return a dynamic total for each visual. A calculated column stores a row-level result during refresh and can increase model size. I would use a column for a stable attribute needed for grouping or filtering, and a measure for aggregations such as revenue or order count.
- explains evaluation timing and filter context.
- chooses based on the model need rather than a blanket rule.
Revenue =
SUMX(
FactOrderLine,
FactOrderLine[Quantity] * FactOrderLine[NetUnitPrice]
)
Orders =
DISTINCTCOUNT(FactOrderLine[OrderID])
Average Order Value =
DIVIDE([Revenue], [Orders])Explain the Work, Not Just the Answer
16. How do you respond when a stakeholder says churn increased?
I would clarify the churn definition, affected population, comparison period and decision they need to make. I would validate source history and metric freshness, inspect the trend and denominators, and segment by useful customer attributes. I would separate an observed pattern from a cause and propose a test or data check that could distinguish plausible explanations.
- scopes the question before choosing an analysis.
- communicates evidence, uncertainty and an actionable next step.
17. Tell me about an analysis that changed your mind.
I would describe a real project where an initial explanation did not survive validation. I would explain what I expected, which data check or counterexample changed my view, how I communicated the correction, and what decision followed. I would be specific about my own contribution and avoid overstating the business impact if it was not measured.
- intellectual honesty and a concrete validation step.
- clear personal contribution and a grounded outcome.
18. How would you approach an unfamiliar take-home assignment?
I would read the requirements, list assumptions and identify the intended audience before coding. I would inspect schema, quality and missingness, create a reproducible analysis with a small number of focused questions, and validate key calculations. I would deliver a short README or memo covering definitions, findings, limitations and how to rerun the work, while following the assignment's time and data-use constraints.
- prioritizes scope and reproducibility over unnecessary polish.
- provides caveats, validates outputs and respects data handling instructions.
Deliver a Small, Auditable Analysis
Keep a take-home reproducible and proportionate to the brief. Show focused findings, distinguish source facts from interpretation, and make the work easy to rerun.
- Write down the decision, audience and required output.
- Inspect grain, keys, missing values and date coverage.
- Build reproducible code with assumptions stated.
- Validate totals and inspect edge cases or a small sample.
- Present a headline, evidence, limitations and next step.
| Deliverable | What it should show |
|---|---|
| Query or notebook | Reproducible calculations with assumptions stated |
| Validation note | Checks for grain, totals, missingness and edge cases |
| Recommendation | Decision, supporting evidence, caveat and next action |
Common Interview Mistakes
Starting to code before clarifying the metric
Why: Different grains, dates or tie rules can produce different answers.
Fix: Ask a concise clarifying question and state assumptions before implementation.
Claiming causation from a segment difference
Why: The groups may differ in tenure, intent or other confounders.
Fix: Describe association honestly and propose an appropriate causal design.
Giving a technically correct result with no decision context
Why: The stakeholder cannot tell what to do next.
Fix: End with a plain-language implication, limitation and next action.
Hiding failed checks or personal contribution
Why: It makes the work hard to trust and difficult to reproduce.
Fix: Explain what you validated, what changed, and exactly what you owned.
Run a Mock Interview and Review Your Answers
Practice
Answer aloud, then verify the technical details
- Choose four SQL questions and explain the grain and assumptions before writing the query.
- Explain a confidence interval and a p-value to a product manager without using them as a decision by themselves.
- Give a two-minute answer to the churn case using observed evidence, limitations and one next test.
- Describe one real project with your specific contribution, a quality check and a decision outcome.
- Complete a small take-home: SQL query, validation notes, one chart and a short recommendation memo.
Hint: Record one practice answer. Remove filler, keep the assumptions, and make sure the conclusion follows from the evidence.
🎯 Key Takeaways
- ✓Clarify the decision, population, grain and time window before selecting a method.
- ✓In SQL, handle ties, duplicates, nulls and missing dates intentionally; validate results on examples.
- ✓Interpret statistical evidence with assumptions, effect size and study design in view.
- ✓Product metrics need a meaningful behavior definition and guardrails against misleading optimization.
- ✓A good analyst separates observations, hypotheses and causal conclusions.
- ✓Take-home work should be reproducible, auditable, privacy-aware and concise.
- For role-specific expectations, review the job description and the documentation for the SQL, BI and analysis tools used by the team.
Next
What comes next
Continue strengthening the query skills behind analyst work with the dedicated SQL learning path.
Next course · SQL →HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
