Data Analyst Interview Preparation: SQL, Metrics and a Worked Business Case

TL;DR

• Prepare to turn an ambiguous business question into a defined metric, a checked dataset and a clear recommendation.
• Practice SQL at the correct row grain, especially joins that can duplicate totals and filters that remove missing cases.
• Explain assumptions and data-quality checks before presenting an attractive chart.
• Rehearse a small original case end to end, including the result, its limits and the decision it supports.

Start with the decisions the role supports

Data analyst interviews can emphasize different work: reporting, product behavior, operations, finance or experimentation. Read the actual role description and identify the recurring decisions the analyst is expected to support.

Build a preparation map around those decisions. A role concerned with product onboarding may need cohort definitions and experiments. An operations role may focus on backlog, service times and exceptions. SQL can appear in either, but the business question determines what a correct result means.

Ask about the interview format when possible. A live query exercise, a take-home case and a discussion of your past projects require different preparation. Do not assume a universal sequence of rounds or memorize a company's alleged question list.

Choose one project you genuinely understand and prepare to explain its question, data, method, validation and practical outcome. If you use the fictional exercise in this article, describe it as practice rather than your employment experience.

Define the metric before querying

Suppose a fictional account team asks, “Which customers need attention?” That question is too broad to answer with a single query. Ask whether attention means onboarding help, an unresolved service problem, inactivity or a commercial follow-up. Clarifying a vague request this way is also where a five-step answer structure for technical case study interviews begins.

For a small practice case, narrow the request: produce one row per customer showing paid order total and review count. Include customers with no paid orders. Exclude cancelled orders from the paid total. This creates a concrete output contract without pretending that spending and review count alone define customer health.

Write the grain of each source table. Customers has one row per customer, orders has one row per order and reviews has one row per review. A customer can have several orders and several reviews. That last sentence should make you cautious about joining both detail tables before aggregating.

Also clarify units. The exercise stores order totals in integer cents for one currency. If the real data contains several currencies, a combined sum needs a conversion and reporting policy before it becomes meaningful.

Practice the join that can double your answer

PostgreSQL's join tutorial explains how matching rows are combined and how an outer join preserves unmatched rows on one side. For this exercise, joining each order to each review from the same customer creates multiple combinations.

Imagine customer C1 has two paid orders worth 1,000 and 2,000 cents, plus two reviews. Joining orders directly to reviews produces four matched rows. Summing the order totals there gives 6,000 cents, although the real paid total is 3,000. One of the SQL interview questions on joins and window functions shows the same bug when orders are joined to order items and payments at once.

Using DISTINCT on the amount is not a sound repair. Two legitimate orders can have equal amounts. The correct design should preserve the order grain, not assume that every repeated amount is a duplicate.

Aggregate each detail table to the intended customer grain before combining it:

WITH paid_orders AS (
  SELECT customer_id,
         SUM(total_cents) AS paid_total_cents
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
),
review_counts AS (
  SELECT customer_id,
         COUNT(*) AS review_count
  FROM reviews
  GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(p.paid_total_cents, 0) AS paid_total_cents,
       COALESCE(r.review_count, 0) AS review_count
FROM customers AS c
LEFT JOIN paid_orders AS p
  ON p.customer_id = c.customer_id
LEFT JOIN review_counts AS r
  ON r.customer_id = c.customer_id
ORDER BY c.customer_id;

This query intentionally reports all-time paid totals in the supplied practice data. A real reporting period would need an explicit date filter and time-zone policy. Do not label an unfiltered total as monthly revenue.

Test the result with a tiny fixture

Use data small enough to inspect manually before running a query over millions of rows. The original fixture for this exercise contains three customers:

Check that the result has exactly three rows and a combined paid total of 5,500 cents. Then add a second C1 paid order for 1,000 cents. The total should rise to 4,000 for C1, proving that equal-valued legitimate orders are not discarded.

Next, remove all reviews from C2. Its paid total should remain 2,500 and its review count should become zero. This catches a design that accidentally requires every customer to have a review.

PostgreSQL's aggregate reference is useful when checking how aggregates handle empty inputs and nulls. In the exercise, COALESCE gives an explicit zero for a missing aggregate row; it should not be used to hide unexplained missing source values.

Explain when a window function helps

A separate question might ask for the latest paid order per customer while retaining the order's fields. A grouped maximum timestamp alone does not necessarily identify a unique row, especially when timestamps tie.

An original ranking query is:

WITH ranked_orders AS (
  SELECT order_id, customer_id, created_at, total_cents,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, order_id DESC
         ) AS row_rank
  FROM orders
  WHERE status = 'paid'
)
SELECT order_id, customer_id, created_at, total_cents
FROM ranked_orders
WHERE row_rank = 1
ORDER BY customer_id;

PostgreSQL's window-function tutorial explains that window calculations preserve individual rows and that ordering affects row numbering. Here, order_id provides a deterministic tie-breaker. It does not prove which tied order happened later in the real world; that would need a more precise business ordering rule.

Customers without a paid order are absent from this second result. If the interviewer requires every customer, explain how you would join the ranked result back to customers. Do not assume two different questions must return the same population.

Prepare for data-quality questions

List checks that could change the interpretation: duplicate identifiers, missing relationships, unexpected statuses, impossible values, incomplete periods and delayed records. Choose checks based on the data rather than reciting every test you know.

For the order case, a duplicated order ID should trigger investigation rather than a casual DISTINCT. A negative total might be invalid in this dataset or represent a refund in another system. Ask which interpretation the contract supports.

Reconcile important totals against an independent trusted source when one exists. Record differences and the extraction time. A query that ran successfully has not automatically reconciled the business data.

Describe how you would communicate a limitation. “The last day is incomplete because the source arrives the following morning” is more useful than silently presenting a falling line as a business decline.

Practice explaining uncertainty and causality

An analyst may observe that customers using a feature retain better. That observation alone does not establish that the feature caused retention; those customers may differ in motivation, plan or tenure.

Explain what additional evidence would help: a better-defined comparison, appropriate adjustment, a controlled experiment or a clearer measurement design. Do not claim that adding more charts resolves a selection problem.

The A/B testing interview guide provides a separate worked example of assignment, guardrails and uncertainty. Keep descriptive analysis and causal claims distinct when presenting your own project.

If the data cannot answer the question, say what it can answer and what should be collected next. That is an analytical result, not a failure to produce a dashboard.

Rehearse a concise stakeholder explanation

Present the case in a simple order: the decision, the metric definition, the result, the main validation and the limitation. For the practice fixture, explain that the paid total excludes cancelled orders and that review counts were aggregated separately to prevent multiplication of order amounts. You can rehearse this explanation aloud in an AI mock interview practice session, which ends with written feedback and a full transcript.

Choose a chart only when it helps the decision. A three-customer table may be clearer than a decorative visualization. For a larger population, state the sorting, units, period and treatment of missing data.

Finish with a practical next step. If the request was really about customer support needs, spending and review count are only a starting point; you may need unresolved-ticket status or recent product activity. Explain that additional requirement rather than presenting the toy query as a complete customer-health system.

Good preparation combines technical correctness with the ability to explain what the result means. Practice both with small, inspectable examples before adding scale or complexity.