Data Analyst Interview Questions and Answers
Data analyst interview questions with answers: SQL, Excel and spreadsheets, statistics, metrics and dashboards, data cleaning, A/B tests, case questions and communicating insights.
Key takeaways
- Data analyst interviews are built around SQL: joins, aggregation and window functions on a realistic schema.
- Expect metrics and case questions: define a KPI, investigate a drop, size an opportunity.
- Communication is assessed directly: explaining a finding to a non-technical stakeholder in two minutes.
- Many companies add a take-home or spreadsheet task; clarity of the final summary matters as much as the analysis.
Contents
Data analyst interviews focus on SQL, metrics and communication: can you get the right numbers, interpret them correctly and explain what they mean? Below are 31 common questions grouped by topic and level, with answers and examples.
What data analyst interviews cover
Editorial estimate; BI-focused roles add more dashboard and tooling questions.
A typical analyst interview process
See also SQL interview questions and data scientist interview questions.
SQL
1What is the difference between WHERE and HAVING?
JuniorWHERE filters rows before aggregation, HAVING filters groups after it. For example, “customers with more than 5 paid orders” needs WHERE status = 'paid' and HAVING COUNT(*) > 5.
2Explain the types of joins and when you’d use a LEFT JOIN.
JuniorINNER returns matching rows only; LEFT keeps all rows from the left table; FULL keeps all rows from both. Use LEFT JOIN when you need every record from the main table even without a match, for example all users including those with no orders.
3Write a query for monthly revenue and month-over-month growth.
MidWITH m AS (
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS revenue
FROM orders WHERE status = 'paid' GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1), 1) AS mom_pct
FROM m ORDER BY month;
4Build a monthly signup cohort retention table.
MidAssign each user a cohort (signup month), compute months since signup for each activity, and count distinct users:
WITH c AS (
SELECT user_id, date_trunc('month', signup_date) AS cohort FROM users
), a AS (
SELECT DISTINCT e.user_id, c.cohort,
(EXTRACT(YEAR FROM age(date_trunc('month', e.event_date), c.cohort)) * 12
+ EXTRACT(MONTH FROM age(date_trunc('month', e.event_date), c.cohort))) AS month_n
FROM events e JOIN c USING (user_id)
)
SELECT cohort, month_n, COUNT(*) AS active_users
FROM a GROUP BY cohort, month_n ORDER BY cohort, month_n;
Divide by the cohort size to get retention percentages.
5How do you find the top 3 products per category?
MidRank with a window function in a CTE and filter on the rank: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC). Use DENSE_RANK if ties should share a position. More patterns are in our SQL interview questions.
6Why might your totals be higher after a join?
MidThe join key isn’t unique on one side, so rows are duplicated (fan-out). Aggregate the detailed table to the join grain first, or check uniqueness with COUNT(*) vs COUNT(DISTINCT key) before joining.
Excel and BI tools
7VLOOKUP vs XLOOKUP vs INDEX/MATCH?
JuniorVLOOKUP searches the first column and returns a column to the right; it breaks when columns move. INDEX/MATCH works in any direction. XLOOKUP (newer Excel) combines both with clearer syntax, exact match by default and a built-in “not found” value.
8What is a pivot table used for?
JuniorTo summarize data quickly: totals, counts and averages by category and time, with filters and drill-down. It is the fastest way to explore a dataset before building a proper model or dashboard.
9How do you design a good dashboard?
MidStart from the decisions it should support and the audience. Put the few key metrics at the top with context (targets, previous period), use simple charts, keep consistent definitions, add filters only where they help, and document where the data comes from and how often it refreshes.
10Which chart would you use for…?
MidTrend over time: line chart. Comparing categories: bar chart, sorted. Part of a whole with few categories: stacked bar (pie only for 2–3 parts). Distribution: histogram or box plot. Relationship between two variables: scatter plot. Avoid 3D and dual-axis charts that distort comparisons.
Get Data Analyst answer points in real time during your interview
Sottena listens to the interviewer, detects each question and shows short answer points in about a second. 150 free credits, no card required.
Statistics
11Mean vs median: when is each better?
JuniorThe mean uses all values but is pulled by outliers; the median is the middle value and robust to them. For skewed data like income, order values or session length, the median usually describes a “typical” case better. Report both when the difference matters.
12What is standard deviation?
JuniorA measure of how spread out values are around the mean. For roughly normal data, about 68% of values fall within one standard deviation of the mean and about 95% within two.
13How do you know if a difference between two groups is real?
MidUse a statistical test suited to the metric (a two-proportion z-test for conversion rates, a t-test for means), check the sample size and confidence interval, and consider practical significance: a statistically significant 0.1% lift may not matter for the business.
14What is Simpson’s paradox?
MidA trend that appears in several groups reverses when the groups are combined, because the group sizes differ. Example: a new checkout converts better on both mobile and desktop but worse overall, because more of its traffic came from mobile. Always check segment mix.
15How would you analyze an A/B test result?
MidCheck that groups are balanced (sample ratio mismatch), compare the primary metric with a confidence interval, check guardrail metrics, look for consistency across key segments, and avoid stopping early. Report the effect size and uncertainty, not just “significant or not”.
Metrics and product sense
16What KPIs would you track for an e-commerce site?
JuniorTraffic and conversion rate, average order value, revenue, customer acquisition cost, repeat purchase rate and retention, cart abandonment, and return rate. Choose a few that match the current business goal rather than tracking everything.
17How do you define an active user?
MidBased on meaningful actions, not logins alone: for a messaging app, sending a message; for a SaaS tool, completing a core workflow. Pick a time window (daily, weekly, monthly) that matches how often people are expected to use the product, and keep the definition stable.
18Weekly active users dropped 15%. How do you investigate?
Mid- Validate the data: tracking changes, pipeline delays, definition changes.
- Check timing and external causes: holidays, outages, releases, marketing changes.
- Segment: platform, app version, country, channel, new vs returning users.
- Walk the funnel to find where users drop.
- Form hypotheses, test them with data, and report what you found and what’s still unknown.
19What is a north star metric?
MidOne metric that best captures the value customers get from the product and predicts long-term success, such as weekly active teams for a collaboration tool. It is supported by input metrics the teams can influence directly.
20How would you estimate the impact of a feature before building it?
SeniorSize the opportunity: how many users are affected, their current behavior, and a realistic range for the change based on benchmarks or past experiments. Present a low, base and high scenario with the assumptions spelled out, and suggest how to validate them cheaply.
Data cleaning and quality
21How do you handle missing data?
JuniorFind out why it is missing. Then remove, fill (median, previous value, a category like “unknown”) or flag it. Document what you did, because it can change results.
22How do you deal with outliers?
JuniorCheck whether they are errors or real extreme values. Fix or remove errors; for real values, use robust statistics (median, percentiles), cap extreme values, or analyze them separately. Never delete them silently.
23How do you make sure your numbers are right?
MidReconcile with a trusted source (finance, another report), check row counts and totals at each step, look for duplicates after joins, test edge cases like NULLs and time zones, and have someone review the query for critical numbers.
24What problems do time zones cause in analysis?
MidEvents logged in UTC but reported in local time shift across day boundaries, so daily totals don’t match other reports. Agree on one reporting time zone, convert explicitly, and be careful with daylight saving time changes.
Python for analysts
25Which Python libraries do analysts use?
Juniorpandas for data manipulation, NumPy for numerics, matplotlib or seaborn (or Plotly) for charts, SciPy and statsmodels for statistics, and Jupyter notebooks for exploration.
26In pandas, how do you compute the share of revenue by category?
Midshare = (df.groupby("category")["revenue"].sum()
.pipe(lambda s: s / s.sum())
.sort_values(ascending=False))
27merge vs concat vs join in pandas?
Midmerge joins DataFrames on columns like SQL joins. concat stacks them vertically or horizontally. join is a convenience for joining on the index. Check the result’s row count after every merge.
Communication and case studies
28How do you explain a technical finding to a non-technical stakeholder?
JuniorLead with the conclusion and what it means for them, support it with one clear chart and one or two numbers, mention the confidence and limitations in plain words, and end with a recommendation or next step.
29A stakeholder asks for a number by tomorrow that you think is misleading. What do you do?
MidUnderstand the decision behind the request, provide the number with clear context, and offer a better metric or an additional view that answers the real question. Be transparent about the limitation rather than refusing.
30Tell me about an analysis that changed a decision.
MidUse the STAR method: the business question, the data and approach, the finding (with a number), the decision it led to, and the measured impact. Interviewers look for problem framing and influence, not just technical work.
31How do you prioritize many analysis requests?
SeniorBy the size of the decision each request supports and its urgency. Clarify the question and deadline with each requester, offer quick answers where a rough number is enough, build self-serve dashboards for repeated questions, and keep a visible queue.
How to prepare
- Practice SQL daily on business dataRevenue, cohorts, funnels, retention and top-N per group.
- Prepare a metrics frameworkBe ready to define KPIs for an e-commerce, SaaS or marketplace product.
- Rehearse a metric-drop caseData checks, external causes, segmentation, funnel, hypotheses.
- Polish one portfolio projectA clear question, clean analysis and a one-page summary with a recommendation.
- Practice explaining findings in two minutesConclusion first, one chart, one recommendation.
Get answer points in real time during your interview
Sottena listens to the interviewer, detects each question and shows short answer points in about a second. 150 free credits, no card required.