Data analyst interview questions fall into four rounds that most companies run in some form: SQL (usually a live exercise), statistics and experimentation, a case or take-home on a business problem, and behavioural questions about stakeholders and mistakes. The SQL round eliminates the most candidates, the case round decides between the rest. Below are 40 data analyst interview questions with sample answers across all four, the SQL patterns that come up in almost every screen (window functions, joins with filters, aggregations with conditions), how to structure a case answer, and the questions to ask that reveal whether the role is analysis or reporting.
SQL interview questions for data analysts
Expect to write queries live, on a shared screen, against a schema you are shown two minutes earlier. Talk while you write: the interviewer is scoring your reasoning as much as the syntax. The patterns below cover most screens.
SQL questions and how to answer them
Use a window function: rank salaries within each department with DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC), then filter where the rank equals 2. Mention why DENSE_RANK rather than ROW_NUMBER: ties. Then mention the edge case: departments with only one salary return nothing, and ask whether that is the desired behaviour.
Aggregate revenue by month, then use LAG(revenue) OVER (ORDER BY month) to get the previous month, and compute (revenue - previous) / previous. Say what you do with the first month (NULL) and with months that have no rows (a calendar table, if the interviewer wants completeness).
Two approaches: a LEFT JOIN from January customers to February customers filtered where the February side is NULL, or a NOT EXISTS subquery. Say that NOT EXISTS handles NULL customer IDs more safely than NOT IN. Then ask how the interviewer defines a purchase: an order row, a paid order, a non-refunded order.
WHERE filters rows before aggregation; HAVING filters groups after it. "Customers with more than five orders" needs HAVING COUNT(*) > 5; "orders over 100 dollars" is a WHERE. Give a case where confusing them changes the result.
INNER keeps matches; LEFT keeps all left rows; RIGHT and FULL accordingly. Duplicates appear when the right table has multiple rows per key, and the fix is to aggregate the right table first or to confirm the key is unique. Interviewers like candidates who check row counts after a join.
SUM(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Explain the frame clause and what happens with duplicate days if you use RANGE instead.
GROUP BY the columns that define a duplicate with HAVING COUNT(*) > 1, or ROW_NUMBER() partitioned by those columns and filter rn > 1 to return the extra rows themselves. Ask which columns define identity before you write it.
Look at the execution plan; check indexes on join and filter columns; avoid functions on indexed columns in WHERE; filter before joining; avoid SELECT *; consider whether a materialised aggregate or a CTE with early filtering helps. Name one real query you sped up and by how much.
Statistics and experimentation questions
- Explain a p-value to a non-technical manager. "If there were really no difference, how often would we see a result at least this big by chance? A p-value of 0.03 means about 3 times in 100." Then say what it is not: the probability the result is wrong.
- How would you design an A/B test for a new checkout page? Hypothesis and primary metric (conversion rate), guardrail metrics (revenue per user, error rate), randomisation unit (user, not session), sample size from a power calculation with the minimum detectable effect the business cares about, run time covering at least one weekly cycle, and a pre-registered decision rule. Mention peeking and how you avoid it.
- What is the difference between correlation and causation? Give an example from your work. Correlation is co-movement; causation needs a mechanism and, ideally, an experiment or a natural experiment. Use a real example: app users who enable notifications retain better, but they were already more engaged; the test that separated the two.
- How do you handle missing data? Understand why it is missing first (random, or related to the value itself), then choose: drop, impute (mean, median, model-based), or model missingness as a category. Say which you would pick for a specific column and why.
- What is a confidence interval? A range that, under repeated sampling, would contain the true value a stated share of the time. Give one from a real analysis and how you presented it ("between 4% and 9% uplift").
- Explain overfitting to someone who has never built a model. A model that memorised the past instead of learning the pattern; it scores well on data it has seen and badly on new data. Say how you detect it (held-out data, cross-validation) and prevent it (simpler models, regularisation, more data).
- When would you use a median instead of a mean? When the distribution is skewed or has outliers: income, order value, time on page. Give an example where the mean misled a stakeholder.
Business case and take-home questions (and how to structure the answer)
A case question gives you a business problem ("sales dropped 15% last month; find out why") and scores your structure more than your conclusion. Use the same shape every time: clarify the metric and the timeframe, break the metric into its components, ask which data exists, propose the first three cuts, say what you would do with each result, and end with a recommendation and a caveat.
- Sales dropped 15% last month. How would you investigate? Clarify: which sales metric, versus what baseline, one month or a trend. Decompose: sales = customers × conversion × order value. Cut by segment, channel, product, region and new vs returning. Check data quality first (tracking changes, pipeline failures). Then look for what changed: pricing, competitors, seasonality, a site release. End with the two most likely causes and the analysis to confirm each.
- How would you measure the success of a new feature? Define the outcome the feature is meant to change, a primary metric and two guardrails, the comparison (A/B test or pre/post with controls), the time window, and how you would present it to the product manager. Say what you would conclude if the primary moved but a guardrail worsened.
- We want to reduce customer churn. Where would you start? Define churn precisely, measure it by cohort and segment, find the point in the lifecycle where it concentrates, identify behaviours that precede it, and propose an intervention with a way to test it. Mention that a churn model is step four, not step one.
- Take-home: here is a dataset; find insights. Structure the deliverable: one-page summary with three findings and a recommendation, then the analysis, then an appendix with the code and data-quality notes. Interviewers read the first page and the data-quality notes; both are where candidates are separated.
Behavioural questions for data analysts
- Tell me about an analysis that changed a decision.
- Describe a time your analysis was wrong. What happened?
- Tell me about a stakeholder who did not accept your findings.
- Describe a time you had to explain a technical result to a non-technical audience.
- Tell me about a time you had to work with messy or incomplete data.
- Describe a dashboard you built. Who used it, and how do you know?
- Tell me about a time you pushed back on a request for analysis.
- How do you prioritise ad-hoc requests against planned work?
Answer in STAR with a number in the Result: "the campaign it targeted reactivated 9.6% of 18,000 accounts" or "the dashboard is opened by three regional heads every Monday". A dashboard nobody uses is a common honest answer to the wrong question; say what you learned about asking who the user is first.
Tools questions (Excel, Python, BI)
- How would you do a VLOOKUP-style join in Excel, and when would you move the work to SQL or Python?
- Explain pivot tables and a case where they were the right tool.
- What Python libraries do you use for analysis, and for what?
- How do you handle a Power BI or Tableau dashboard that has become slow?
- How do you version and document your analysis?
- When would you write a script rather than a query?
Questions to ask that reveal what the job really is
- What share of the role is ad-hoc requests versus owned analysis or projects?
- Who are the main stakeholders, and how do they currently make decisions without this analysis?
- What does the data stack look like, and who owns data quality?
- Can you give an example of an analysis from this team that changed a decision in the last six months?
- How is the team's work prioritised, and by whom?
- What would you expect me to have delivered in the first 90 days?
Practise the SQL and case rounds out loud
The AI interview tool asks data analyst questions, including SQL reasoning and case structure, and scores your answers on clarity and completeness.
Start a mock analyst interviewData analyst interviews: practical questions
Practise SQL until window functions, conditional aggregation and join pitfalls are automatic; prepare two analyses with numbers (what changed because of them); revise A/B testing, p-values and confidence intervals in plain language; prepare a case structure; and prepare STAR stories for a wrong analysis, a sceptical stakeholder and messy data.
Second-highest per group (window functions), month-over-month change (LAG), customers in one period but not another (LEFT JOIN or NOT EXISTS), duplicates, running totals, WHERE vs HAVING, join behaviour, and query optimisation. Expect to write at least one live.
Usually a live SQL exercise, a statistics conversation at the level of explaining concepts to a manager, and a case or take-home. Python is asked about but rarely tested live unless the posting emphasises it.
One analysis, the question it answered, the method in one sentence, the finding with a number, and the decision that followed: "the SQL identified 18,000 dormant-but-eligible accounts, the campaign reactivated 9.6%, and the bank made it a quarterly programme."
Ask what share of the role is ad-hoc reporting versus owned analysis, and for an example of an analysis that changed a decision in the last six months. The answers tell you whether the job is analysis or reporting.
Share article