Data Analyst Case Interview: How to Investigate a Metric Drop Without Guessing

A metric-drop case is not a contest to name the most plausible cause. When conversion, revenue, retention, or another KPI suddenly declines, the interviewer wants to see whether you can turn an ambiguous alert into a defensible business decision.

Strong candidates follow a deliberate sequence: define the metric, verify the decline, locate where it occurred, develop testable explanations, and recommend an action with appropriate safeguards. This approach prevents two common failure modes: guessing at causes too early and treating the case as only a SQL exercise.

What a metric-drop case is testing

The case usually tests several skills at once:

• Metric judgment: Can you define the KPI, population, and comparison correctly?
• Data skepticism: Can you distinguish a business problem from a tracking or pipeline problem?
• Structured diagnosis: Can you break a broad decline into smaller, measurable components?
• Prioritization: Can you test likely, high-impact explanations before exploring every possible slice?
• Communication: Can you separate confirmed facts, hypotheses, and causal claims?
• Decision quality: Can you recommend a proportionate response and define how success will be monitored?

You do not need perfect data or a complete schema to demonstrate these abilities. You do need to make your assumptions explicit.

1. Clarify the metric and the decision

Begin by restating the problem precisely. “Conversion fell” is incomplete. Ask what event defines conversion, which users are eligible, whether users or sessions are counted, how long they have to convert, and which timezone determines the reporting day.

Then clarify the comparison. A complete Monday-through-Sunday week should usually be compared with another complete week, not a partial current week. Cohort metrics may require a maturity delay: yesterday’s signup cohort cannot have a seven-day activation rate yet. Consider holidays, promotions, billing cycles, and day-of-week patterns before accepting the default comparison window.

Finally, identify the decision behind the analysis. Is the team deciding whether to roll back a release, pause a campaign, alert a partner, or change a forecast? The decision determines the required speed and confidence. A reversible release rollback may justify acting on strong evidence before causality is proven; a permanent product redesign requires more certainty.

2. Confirm that the drop is real

Before explaining the business, validate the measurement. Check:

• Whether source tables and dashboards refreshed successfully.
• Whether all expected platforms, regions, partitions, and event sources are present.
• Whether the numerator or denominator event changed names, payloads, or tracking behavior.
• Whether dashboard filters, joins, exclusions, or identity logic changed.
• Whether the metric definition was edited or backfilled.
• Whether recent data is late and the newest cohorts are incomplete.
• Whether timezone or daylight-saving boundaries shifted events between dates.

If the warehouse records event and ingestion timestamps, a bounded read-only query can expose volume gaps and unusual lag. Adapt the table and event names to the case:

SELECT
    date_trunc(
        'day',
        event_at AT TIME ZONE 'America/Los_Angeles'
    ) AS business_day,
    COUNT(*) FILTER (
        WHERE event_name = 'signup_eligible'
    ) AS eligible_events,
    COUNT(*) FILTER (
        WHERE event_name = 'signup_completed'
    ) AS completion_events,
    COUNT(DISTINCT user_id) AS observed_users,
    MAX(ingested_at - event_at) AS maximum_ingestion_lag
FROM analytics.events
WHERE event_at >= TIMESTAMPTZ '2026-08-03 07:00:00+00'
  AND event_at <  TIMESTAMPTZ '2026-08-17 07:00:00+00'
  AND event_name IN ('signup_eligible', 'signup_completed')
GROUP BY 1
ORDER BY 1;

Do not declare “tracking issue” simply because the decline is surprising. State what evidence would confirm it, such as a missing Android partition, a sudden change in event-to-server-record reconciliation, or ingestion lag beyond the metric’s normal completion window.

3. Decompose the metric mathematically

Once the decline is credible, use the metric’s equation to organize the investigation.

For conversion:

Conversion rate = converted eligible users / eligible users

A lower rate can result from fewer conversions, a larger denominator, or both. If conversions are stable but a campaign adds many low-intent visitors, the product may not have deteriorated even though the blended rate fell.

For revenue, use a multiplicative decomposition appropriate to the business:

Revenue = traffic × conversion rate × orders per buyer × average order value

Also inspect additive components such as product line, country, or customer tier. Quantify contribution in absolute units where possible. Saying “mobile declined most” is less useful than saying “mobile accounts for approximately 75% of the lost signups.”

Be alert to composition effects. A blended metric can fall even when every segment is stable if traffic shifts toward segments with lower normal conversion. Conversely, an apparently small blended change can conceal a severe decline in an important segment.

4. Segment with a reason, not by fishing

Choose dimensions connected to plausible mechanisms:

• Platform, operating system, browser, and app version for experience or release issues.
• Acquisition channel and campaign for traffic-quality changes.
• New versus returning users for lifecycle differences.
• Country, payment method, or fulfillment region for operational failures.
• Product, plan, or funnel step for localized product problems.

Start with a few mutually exclusive, sufficiently large segments. For each, compare volume, rate, absolute loss, and share of the total change. Avoid chasing tiny groups with dramatic percentages. If you inspect dozens of dimensions until one looks unusual, you increase the chance of finding noise and make the story harder to defend.

5. Align the decline with a timeline

Find the inflection point at the finest useful grain, then place relevant events around it: deployments, feature-flag changes, campaigns, price changes, outages, partner incidents, holidays, and recurring seasonal patterns.

Timing helps prioritize hypotheses, but coincidence is not causality. A release that preceded the decline is a candidate explanation. The case becomes stronger if the effect appears only on the released version, begins as adoption rises, matches a known error mode, and reverses after rollback.

6. Build and prioritize a hypothesis tree

A compact hypothesis tree keeps the analysis complete:

• Measurement: missing data, definition drift, tracking changes, late events.
• Population and mix: channel, geography, device, or customer composition changed.
• Product experience: release regression, broken funnel step, performance, or usability issue.
• Operations: inventory, payments, fulfillment, support, or partner failure.
• External environment: seasonality, competitor action, regulation, or macroeconomic change.

Prioritize each branch by likely impact, plausibility, speed to test, and reversibility. A high-impact release that changed at the exact inflection deserves attention before a speculative competitor explanation. Say what query, log, experiment, or operational check would support or reject each leading hypothesis.

Worked example: signup conversion falls from 18.0% to 14.2%

Suppose the interviewer says weekly signup conversion declined from 18.0% to 14.2%. First define it as unique eligible users who complete signup within 24 hours, grouped by the date of eligibility. Compare two complete Monday-to-Sunday cohorts after both have had 24 hours to mature.

After confirming stable definitions, complete partitions, and normal ingestion lag, calculate the numerator and denominator. The prior week had 100,000 eligible users and 18,000 completions. The current week had 120,000 eligible users and about 17,000 completions. The denominator grew while completed signups declined slightly, so traffic growth alone is not a sufficient explanation.

The following PostgreSQL pattern deduplicates eligibility within each period, avoids raw user output, protects division by zero, and uses a bounded cohort window:

WITH bounds AS (
    SELECT
        TIMESTAMPTZ '2026-08-03 00:00:00+00' AS prior_start,
        TIMESTAMPTZ '2026-08-10 00:00:00+00' AS current_start,
        TIMESTAMPTZ '2026-08-17 00:00:00+00' AS current_end
),
eligible_events AS (
    SELECT
        CASE
            WHEN e.event_at < b.current_start THEN 'prior'
            ELSE 'current'
        END AS period,
        e.user_id,
        e.event_at AS eligible_at,
        e.platform,
        e.app_version
    FROM analytics.events AS e
    CROSS JOIN bounds AS b
    WHERE e.event_name = 'signup_eligible'
      AND e.event_at >= b.prior_start
      AND e.event_at <  b.current_end
),
eligible AS (
    SELECT DISTINCT ON (period, user_id)
        period,
        user_id,
        eligible_at,
        platform,
        app_version
    FROM eligible_events
    ORDER BY period, user_id, eligible_at
),
labeled AS (
    SELECT
        e.period,
        e.platform,
        e.app_version,
        EXISTS (
            SELECT 1
            FROM analytics.events AS completed
            WHERE completed.user_id = e.user_id
              AND completed.event_name = 'signup_completed'
              AND completed.event_at >= e.eligible_at
              AND completed.event_at < e.eligible_at + INTERVAL '24 hours'
        ) AS converted
    FROM eligible AS e
)
SELECT
    period,
    platform,
    COUNT(*) AS eligible_users,
    COUNT(*) FILTER (WHERE converted) AS converted_users,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE converted)
        / NULLIF(COUNT(*), 0),
        2
    ) AS conversion_rate_pct
FROM labeled
GROUP BY period, platform
ORDER BY platform, period;

Assume the segment results are:

• Desktop: 20,000 eligible users at 20% conversion in both weeks.
• iOS: 30,000 eligible users at 18% conversion in both weeks.
• Android: volume grows from 50,000 to 70,000 while conversion falls from 17.2% to 10.9%.

The larger Android mix explains only a small part of the blended decline. If current Android traffic had retained its prior 17.2% rate, overall conversion would have remained close to 17.9%. Most of the loss is therefore a within-Android performance problem, not merely lower-quality traffic.

Next, chart Android conversion by hour and app version. Suppose the decline begins Tuesday as version 8.4 adoption rises, while older versions remain stable. Application logs also show a sharp increase in validation errors on the final signup step.

At this point, distinguish the statements carefully:

• Fact: The data is complete, and the decline is concentrated among Android 8.4 users.
• Fact: The inflection aligns with version adoption and an increase in final-step validation errors.
• Hypothesis: A change in version 8.4 prevents some valid users from completing signup.
• Causal claim: Version 8.4 caused the decline. This still requires stronger evidence, such as a controlled rollback, feature-flag comparison, or reproducible defect.

How to narrate a 20–30 minute case

1. Minutes 0–3: Restate the metric, population, time window, magnitude, and business decision.
2. Minutes 3–7: Explain how you would validate freshness, coverage, tracking, definitions, and cohort maturity.
3. Minutes 7–15: Decompose the metric and investigate a short list of mechanism-driven segments.
4. Minutes 15–22: Connect the affected segment to a timeline and test the highest-priority hypotheses.
5. Minutes 22–27: Summarize confirmed facts, remaining uncertainty, and estimated business impact.
6. Minutes 27–30: Recommend an action, validation plan, guardrails, and next update.

Keep a running synthesis rather than listing queries. For example: “The decline is real, it is not explained by reporting lag, and approximately all material loss is concentrated on Android after version 8.4. I would now test the final-step validation change before widening the search.”

Make a recommendation with guardrails

In the example, a reasonable recommendation is to pause the Android 8.4 rollout or disable the suspected validation change while engineering reproduces the error. Monitor signup completion, validation-error rate, crash rate, fraud or invalid-account rate, and downstream activation. Define an owner, a decision threshold, and a review time.

This is stronger than saying “roll back the app.” It connects the action to the evidence, acknowledges uncertainty, and protects against restoring conversion by accidentally weakening an important quality control.

Common mistakes

• Jumping directly to seasonality, competitors, or a recent release without validating the metric.
• Writing SQL before defining eligibility, conversion, grain, timezone, and maturity.
• Looking only at percentages and ignoring numerator, denominator, volume, and absolute impact.
• Segmenting every available dimension without a hypothesis.
• Confusing correlation in a segment with proof of causality.
• Ignoring composition effects or Simpson’s paradox in blended metrics.
• Offering a recommendation without monitoring, guardrails, or a rollback condition.
• Spending the entire interview diagnosing and leaving no time to synthesize.

Practice checklist

• Can I state the metric as an equation?
• Are the population, grain, timezone, window, and maturity explicit?
• Have I ruled out freshness, coverage, tracking, filter, and definition problems?
• Did I inspect numerator and denominator separately?
• Are my segments connected to mechanisms and large enough to matter?
• Can I quantify which component contributed most?
• Did I label facts, hypotheses, and causal claims separately?
• Does my recommendation include an owner, guardrails, and a validation plan?

FAQ

Should I begin a metric-drop case with SQL?

Usually not. Begin with the metric definition and decision. SQL is useful only after you know what should be counted, over which window, and at what grain.

How many hypotheses should I present?

Show that your hypothesis tree is complete, then prioritize two or three branches. Interviewers value a reasoned testing order more than an unranked list of possibilities.

What if the interviewer provides very little data?

State the next evidence you would request and what each possible result would imply. You can still demonstrate a rigorous diagnostic process without inventing findings.

When can I say that a release caused the drop?

Temporal alignment and segment concentration create a strong hypothesis, not proof. A randomized holdout, controlled rollback, feature-flag comparison, reproducible defect, or convincing natural experiment supports a causal conclusion.

What if there is not enough time to finish the analysis?

Synthesize what is known, name the largest unresolved uncertainty, and recommend the next highest-value test. A well-scoped conclusion is better than rushing through many unsupported explanations.

The core habit is simple: earn the right to explain the decline. Define it, verify it, decompose it, locate it, test the leading explanation, and connect the evidence to a safe business decision.