Data analyst case interview: 8 practice questions and how to answer them

-- the query is correct
AUTUMN20 orders   €91.81 average
without the code  €36.60 average

Many data analyst interviews have two technical parts. The SQL round asks you to write a query that returns a given result. The case round asks something like “revenue fell 15% last week — what do you do?” and watches how you think. Preparing only query syntax leaves you with less practice explaining those decisions. Interview formats vary; these examples are practice prompts, not a prediction of what a particular employer will ask.

Short version: in a case, don’t start with a query. Clarify the question and the definition, check that the number itself is real, split it into parts, form hypotheses you can test, and end with a recommendation and what would change it. Say every step out loud.

A structure that works for almost any case

1. Clarify. What decision depends on the answer? Which definition of the metric? Which period, compared with what?
2. Check the number. Is the drop real — complete data, no tracking change, no one-off event, same definition as before?
3. Decompose. For a consistently defined cohort and period: goods value = eligible users × (buyers / eligible users) × (paid orders / buyers) × (goods value / paid orders). Find which factor moved; do not mix populations or money measures.
4. Segment. Where is the change concentrated: platform, country, channel, category, new vs returning?
5. Hypothesise and test. For each candidate cause, say what data would confirm or reject it.
6. Recommend. What to do now, how sure you are, and what you would check next.

Eight questions to practise

1. “Revenue dropped 15% last week. What do you do?”

Start with step 2, not step 3: is last week complete? Did the definition, currency or tracking change? Compare with the same week last year for seasonality. Then decompose and segment. A strong answer finds where the drop is before guessing why.

2. “How would you define an active user for our product?”

There is no universal answer. Ask what the metric is for, pick the action that reflects real value (not just a login), pick the window, and name the exclusions (test accounts, staff, bots). Our game Wrong Number is built on this: one database, three right answers. See also define the metric first.

3. “Our A/B test shows +18% conversion with p = 0.01. Should we ship?”

Check the split before the p-value: are the groups the planned size (sample ratio mismatch)? Was the duration fixed in advance? Is the effect stable over the weeks or a novelty spike? Then talk about the confidence interval and the cost of being wrong. See how long to run an A/B test.

4. “Conversion went up but revenue went down. How?”

Mix and denominators: more low-value orders, a promo that raised conversion but cut the average basket, a traffic source with cheap buyers. Decompose revenue into its factors and show which moved.

5. “Write a query for revenue by category.” (and then: “the total is twice what finance says”)

The classic follow-up tests whether you know join fan-out: joining orders to order items repeats each order per item, so summing the order total counts it several times. See why JOIN multiplies rows.

6. “What is the average order value — and is it the right metric here?”

Give the mean, then say why you’d also report the median and a high percentile for a skewed distribution. See average vs median.

7. “A dashboard and a finance report disagree. Which one is right?”

Often both, for different definitions. Compare definitions line by line: which statuses, which amounts (gross, net of discounts, net of refunds), which time zone and period, which currency.

8. “How would you measure the success of feature X?”

Name one primary metric tied to the goal of the feature, one or two guardrail metrics that must not get worse, the population, and how you’d attribute the change — an experiment if possible.

What interviewers listen for

• You ask clarifying questions before computing.
• You check the number before explaining it.
• You decompose instead of guessing.
• You end with a recommendation, not just an analysis.
• You say what would change your mind.

How to practise

For the SQL round, try our two interactive JOIN interview questions: work out the row count, then fix a sales total that doubled. You can see where each result comes from before moving on.

To practise the broader job, the level check helps you find a starting point, Atterna Start teaches SQL from zero, and the Meridian simulator gives you colleagues’ requests to investigate, each episode ending in a decision backed by your numbers (part of Atterna Analyst, €59). Question banks such as DataLemur or StrataScratch are useful for additional SQL practice — see where to learn data analysis.

FAQ

What is a data analyst case interview?

A round where you get an open business question — a metric changed, a test result, a definition — and talk through how you would answer it. It tests structure and judgement more than SQL syntax.

How do I structure an answer to “metric X dropped”?

Clarify the definition and period, check the drop is real (complete data, no tracking change), decompose the metric into factors, segment to find where it happened, test hypotheses, and finish with a recommendation.

Do I need to write SQL in a case interview?

Sometimes a little. More important is saying which data you would pull and why, and spotting traps such as join duplicates, incomplete periods and the wrong denominator.

How can I prepare in a week?

Learn the six-step structure, practise it out loud on the eight questions above, and solve two or three cases on realistic data where your answer is checked rather than just compared with a model answer.