Data Analyst Interview Case Study: SQL, Metrics, and Business Questions

What this interview guide is optimized for

Many data analyst interview guides are too broad. They list SQL, statistics, dashboards, Python, Excel, and product sense, but they do not show how those skills appear in a real interview case.

This guide focuses on the most common interview pattern: the interviewer gives you a business problem, asks how you would analyze it, then follows up with SQL, metrics, assumptions, and communication questions.

The case study answer framework

Use this structure when you get a question like "conversion dropped last week" or "repurchase rate is falling."

1. Clarify the business goal.
2. Define the metric: numerator, denominator, time window, and segment.
3. Check data quality and tracking changes.
4. Decompose the metric into funnel steps, user segments, or cohorts.
5. Write the SQL or explain the table grain.
6. Summarize the likely driver and recommended next step.
7. State one caveat and one follow-up analysis.

Example case: conversion rate dropped

Interviewer prompt: "Our checkout conversion rate dropped by 12% last week. How would you investigate it?"

A strong answer does not jump directly to "maybe the checkout page is broken." Start by defining the metric and checking whether the drop is real.

Answer outline:
I would first confirm the conversion definition: completed orders divided by checkout starts.
Then I would compare last week with a recent baseline, checking both counts and rates.
Next I would segment the drop by device, traffic channel, geography, new versus returning users, and checkout step.
I would also check tracking changes, payment failures, inventory issues, and campaign mix.
If one segment explains most of the movement, I would recommend a targeted product or marketing investigation rather than a broad site-wide change.

SQL interview question: funnel conversion

This is a common SQL case because it tests joins, grouping, conditional aggregation, and metric definition.

WITH user_funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'checkout_started' THEN event_time END) AS checkout_started_at,
    MIN(CASE WHEN event_name = 'purchase_completed' THEN event_time END) AS purchase_completed_at
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '14 days'
  GROUP BY 1
)
SELECT
  COUNT(*) FILTER (WHERE checkout_started_at IS NOT NULL) AS checkout_users,
  COUNT(*) FILTER (
    WHERE purchase_completed_at IS NOT NULL
      AND purchase_completed_at >= checkout_started_at
  ) AS purchase_users,
  1.0 * COUNT(*) FILTER (
    WHERE purchase_completed_at IS NOT NULL
      AND purchase_completed_at >= checkout_started_at
  ) / NULLIF(COUNT(*) FILTER (WHERE checkout_started_at IS NOT NULL), 0) AS checkout_conversion_rate
FROM user_funnel;

SQL interview question: top revenue categories

This simpler question tests whether you handle filters, revenue logic, grouping, and ordering cleanly.

SELECT
  product_category,
  COUNT(DISTINCT order_id) AS orders,
  SUM(order_value) AS revenue
FROM orders
WHERE order_status = 'completed'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY revenue DESC
LIMIT 5;

Metric definitions interviewers expect

When asked to define a metric, always include the numerator, denominator, time window, and caveats. This is more important than giving a polished sentence.

Conversion rate: users who complete the target action divided by eligible users who entered the funnel.
Retention rate: users active again in a future period divided by users in the original cohort.
Repurchase rate: buyers who purchase again divided by total buyers or eligible existing buyers, depending on the business question.
Average order value: completed order revenue divided by completed orders, excluding refunds and test orders if needed.
Churn rate: customers who stop being active or cancel divided by customers eligible to churn during the period.

How to discuss assumptions

Strong candidates say what they are assuming. For example: "I am assuming users are uniquely identified across devices" or "I am assuming refunded orders should be excluded from revenue." These assumptions make your answer more realistic.

If the interviewer gives limited data, do not freeze. State the table you would want and continue with a reasonable grain, such as one row per event, one row per order, or one row per user per day.

Portfolio story template

Prepare one project story that sounds like an analyst deliverable, not only a dashboard screenshot.

Project story:
Business question: What decision did the project support?
Data: What tables or fields did you use?
Method: What SQL, metric logic, or segmentation did you apply?
Finding: What changed or what pattern did you discover?
Recommendation: What action should the business take?
Caveat: What could make the analysis wrong or incomplete?

Common interview mistakes

Writing SQL before defining the metric.
Forgetting to mention data quality and tracking checks.
Giving a dashboard tour instead of a business recommendation.
Using advanced statistics when the question needs a clean metric definition.
Ignoring the difference between correlation, pre/post comparison, and experiment evidence.
Answering too long without a clear next step.

Practice plan for one week

Day 1: practice metric definitions for conversion, retention, churn, revenue, and repurchase.
Day 2: write SQL for joins, aggregations, date filters, and top-N reports.
Day 3: practice funnel and cohort SQL.
Day 4: answer three business diagnosis cases out loud.
Day 5: polish one portfolio story using the project story template.
Day 6: mock interview with timed answers.
Day 7: review weak assumptions and rewrite your answer structures.