Metric Diagnosis & Segmentation Analysis Questions

Investigating why a metric moved: root-causing a spike, drop, or plateau by decomposing it across segments and dimensions. Covers segmentation, cohorting, Simpson's-paradox traps, and distinguishing a real change from seasonality or a tracking artifact. The scope is diagnostic metric analysis rather than choosing which metric to track.

MediumTechnical

58 practiced

You suspect an instrumentation issue where 'purchase' events were double-counted for a 24-hour window. Describe a statistical approach to estimate the overcount and a SQL query pattern to validate the estimate using raw logs and idempotency keys.

Sample Answer

Approach (summary):- Estimate overcount = (raw rows − deduplicated rows)/deduplicated rows for the 24h window.- Use idempotency_key (or composite keys like user_id+order_id+client_ts) to collapse duplicates.- Get uncertainty via bootstrap or Poisson/binomial CI.- Validate by inspecting duplicate patterns in raw logs (same idempotency_key, very close timestamps, same payload) and compare to baseline days.

SQL patterns (example assumes table events(raw logs) with columns: event_time, event_name, idempotency_key, user_id, payload_hash):

1) Raw vs deduped counts and overcount estimate:

sql

WITH window_events AS (
  SELECT *
  FROM events
  WHERE event_time >= '2025-12-01' AND event_time < '2025-12-02'
  AND event_name = 'purchase'
),
dedup AS (
  SELECT
    COALESCE(idempotency_key, CONCAT(user_id,':',payload_hash)) AS dedup_key,
    MIN(event_time) AS first_ts,
    COUNT(*) AS raw_count
  FROM window_events
  GROUP BY dedup_key
)
SELECT
  (SELECT COUNT(*) FROM window_events) AS raw_rows,
  (SELECT COUNT(*) FROM dedup) AS dedup_rows,
  (raw_rows - dedup_rows) AS estimated_duplicate_events,
  SAFE_DIVIDE(raw_rows - dedup_rows, dedup_rows) AS estimated_overcount_rate
FROM (SELECT 1) t;

2) Inspect duplicate patterns (top duplicate keys):

sql

SELECT dedup_key, COUNT(*) AS occurrences, MIN(event_time) AS first_ts, MAX(event_time) AS last_ts
FROM (
  SELECT
    COALESCE(idempotency_key, CONCAT(user_id,':',payload_hash)) AS dedup_key,
    event_time
  FROM events
  WHERE event_time BETWEEN '2025-12-01' AND '2025-12-02'
    AND event_name='purchase'
)
GROUP BY dedup_key
HAVING COUNT(*) > 1
ORDER BY occurrences DESC
LIMIT 100;

3) Bootstrap CI for overcount (conceptual; run in Python):- Sample dedup rows with replacement, compute overcount rate = (sum(raw_count)-N)/N across samples, get percentiles.

Why this works:- Idempotency keys are the canonical de-duplication signal; comparing raw rows to unique keys gives a direct overcount estimate.- Inspecting timestamps/payloads confirms whether duplicates are retries or distinct events.- Bootstrapping provides uncertainty without parametric assumptions.

Edge cases & considerations:- Missing/NULL idempotency_key → use composite keys but risk false merges.- Keys reused incorrectly across users → add user_id to dedup key.- Systematic day-level shifts: compare to prior days to detect instrumentation drift.

EasyTechnical

79 practiced

Design a diagnostic SQL pattern to identify whether a drop in paid conversions is uniform across payment methods or concentrated in one method (e.g., credit card vs PayPal). Include expected output columns and a suggestion for a visualization to communicate findings.

Sample Answer

Approach: compare paid conversion rates (or absolute paid conversions) by payment_method for a baseline period vs recent period, compute absolute and relative change, and flag methods with disproportionate drops.

sql

-- params: baseline_start, baseline_end, recent_start, recent_end
WITH baseline AS (
  SELECT payment_method,
         COUNT(*) FILTER (WHERE is_paid = 1) AS paid_baseline,
         COUNT(*) AS sessions_baseline
  FROM events
  WHERE event_date BETWEEN :baseline_start AND :baseline_end
  GROUP BY payment_method
),
recent AS (
  SELECT payment_method,
         COUNT(*) FILTER (WHERE is_paid = 1) AS paid_recent,
         COUNT(*) AS sessions_recent
  FROM events
  WHERE event_date BETWEEN :recent_start AND :recent_end
  GROUP BY payment_method
)
SELECT
  COALESCE(r.payment_method, b.payment_method) AS payment_method,
  b.paid_baseline,
  b.sessions_baseline,
  r.paid_recent,
  r.sessions_recent,
  ROUND(100.0 * (b.paid_baseline::float / NULLIF(b.sessions_baseline,0)),2) AS conv_rate_baseline_pct,
  ROUND(100.0 * (r.paid_recent::float / NULLIF(r.sessions_recent,0)),2) AS conv_rate_recent_pct,
  CASE
    WHEN b.paid_baseline IS NULL THEN NULL
    WHEN r.paid_recent IS NULL THEN -100.0
    ELSE ROUND(100.0 * ((r.paid_recent::float / NULLIF(r.sessions_recent,1)) - (b.paid_baseline::float / NULLIF(b.sessions_baseline,1))) / NULLIF(b.paid_baseline::float / NULLIF(b.sessions_baseline,1),1),2)
  END AS pct_change_in_conv_rate
FROM baseline b
FULL OUTER JOIN recent r USING (payment_method)
ORDER BY pct_change_in_conv_rate ASC;

Expected output columns:- payment_method, paid_baseline, sessions_baseline, paid_recent, sessions_recent, conv_rate_baseline_pct, conv_rate_recent_pct, pct_change_in_conv_rate

Interpretation:- A large negative pct_change concentrated on one payment_method indicates non-uniform drop.- If all methods show similar declines, the issue is global (e.g., traffic quality, product).

Visualization suggestion:- Stacked bar or grouped bar chart showing conv_rate_baseline_pct vs conv_rate_recent_pct per payment_method, plus a separate bar/line for pct_change_in_conv_rate. Highlight methods with >X% relative drop (e.g., >20%) in red.

MediumTechnical

67 practiced

A metric shows a significant change, but downstream business KPIs do not move correspondingly. Propose five diagnostic hypotheses for why the leading metric moved without downstream impact and for each propose a fast test or query to validate the hypothesis.

Sample Answer

Situation: A leading metric (e.g., activation rate) jumped but downstream KPIs (revenue, retention) didn't budge. Here are five diagnostic hypotheses and fast tests/queries to validate each.

1) Measurement/Instrumentation bug- Hypothesis: The leading metric is inflated by a tracking bug or duplicate events.- Fast test: Compare unique user counts vs event counts and validate event timestamps/user_ids.- Example query: SELECT user_id, COUNT(*) FROM events WHERE event_type='lead' AND date=’YYYY-MM-DD’ GROUP BY user_id HAVING COUNT(*)>1;

2) Low-quality users- Hypothesis: New users contributing to the leading metric are low LTV (bot, promo-abuse).- Fast test: Cohort new users by acquisition source and compare 7/30-day conversion/retention rates.- Example query: SELECT acquisition_source, COUNT(*) AS users, AVG(day7_retention) FROM cohorts WHERE signup_date>=...

3) Upstream change that doesn't affect downstream funnel step- Hypothesis: The metric change is for an action that isn't causally connected to downstream behavior (vanity metric).- Fast test: Run conditional lift: compare downstream KPI for users who performed the action vs matched controls.- Approach: Propensity-score matching or simple A/B-like comparison.

4) Time lag / delayed effect- Hypothesis: Downstream impact requires more time than observed window.- Fast test: Plot cumulative downstream KPI for the cohort over longer windows (14/30/60 days) to see delayed divergence.

5) Segment-specific effect masking aggregate KPIs- Hypothesis: Increase concentrated in a small segment; aggregate KPIs diluted.- Fast test: Break down leading metric and downstream KPIs by key dimensions (country, device, plan) and compute weighted impact.- Example query: SELECT country, SUM(lead_events), AVG(revenue) FROM ... GROUP BY country;

Result: These quick tests identify whether the spike is real, low-quality, non-causal, delayed, or segment-limited — guiding whether to fix data, target segments, or wait for downstream effects.

HardSystem Design

74 practiced

You're asked to design an anomaly-detection system for hundreds of metrics with varying scales. Propose an architecture (components), statistical or ML algorithms for detection per-metric, and a triage workflow to escalate only the most business-critical incidents to on-call engineers.

Sample Answer

Requirements & constraints:- Ingest hundreds of heterogeneous time-series (different scales, frequencies, cardinalities).- Low false positives for on-call; surface business-impacting incidents only.- Near-real-time detection (minutes), historical baseline retention (months).- Explainability and easy retriage for analysts.

High-level architecture:- Ingest layer: Kafka for streaming metrics + batch S3 landing.- Preprocessing: Spark/Flink jobs to normalize, resample, impute, annotate metadata (owner, SLA, priority).- Feature store: TSDB (Cassandra/InfluxDB/Timescale) + feature snapshots in Parquet.- Detection service: microservices running per-metric models (serverless or Kubernetes).- Scoring & enrichment: compute anomaly score, business impact score, and explainability signals.- Triage engine & alerting: rules + priority queue -> PagerDuty/Slack/Incident DB.- UI: dashboard for analysts with drilldown, suggested root-cause, and feedback loop.

Per-metric detection algorithms:- Baseline selection: choose algorithm by metric profile (seasonality, trend, variance). - Low-volume, seasonal: STL decomposition + z-score on residuals (robust MAD). - Stationary: ARIMA/GARMA or Exponential Smoothing with prediction intervals. - High-frequency, non-linear: online Prophet or SARIMAX for seasonality + rolling quantile. - Multivariate / correlated metrics: Multivariate Gaussian, PCA-based residuals, or LSTM-VAE for learned baselines. - Hybrid: ensemble of statistical (robust) + lightweight ML (isolation forest on features) with weighted voting.- Normalization: use percent-change, scaled to historical rolling windows; use robust scaling to handle outliers.- Online update: incremental learning (online EWMA, incremental tree) to adapt without retraining full models.

Scoring & business impact:- Anomaly score (statistical p-value / normalized residual).- Business impact score = anomaly magnitude × metric weight × downstream dependency weight × SLA breach risk.- Prioritization: thresholding on combined score + cooldown windows to reduce flapping.

Triage workflow:1. Auto-suppress low-impact anomalies by score and owner-configured quiet windows.2. Aggregate related anomalies by correlation and topology (service-metric mapping) into incidents.3. Run enrichment: recent deploys, error logs, latency spikes, correlated metrics, L3 owners.4. Present to on-call only if Business impact score > high threshold OR incident affects SLAs/major customers.5. For mid-impact: create analyst ticket with suggested root cause, allow manual promotion.6. Feedback: analyst/on-call labels feed back to training data to lower FP rate.

Observability & evaluation:- Track precision/recall, mean time to detect, alert volume per day.- A/B test thresholds, use cost-sensitive metrics (cost of missed vs false alert).- Canary new algos on subset of metrics.

Trade-offs:- Statistical methods = explainable, low-cost; ML = better on complex patterns but needs labeled data and compute.- Ensemble balances both: fall back to simple methods when data scarce.

Operational notes:- Enforce metadata (owner, sla) for every metric.- Provide kill-switch and manual override.- Rate-limit alerts; implement cooldown & deduplication.- Monitor model drift and automate model retrain triggers.

MediumTechnical

70 practiced

Scenario: multiple metrics move in different directions after a marketing campaign (revenue up, new user quality down, support tickets up). As growth data scientist, create a prioritized diagnostic playbook of 8 steps (SQL queries, analyses, logs, experiments) you would run in first 48 hours to pinpoint root causes.

Sample Answer

1) Quick cohort & lift check (overall effect): compare key metrics pre/post by campaign cohort to confirm signal.

sql

SELECT cohort, SUM(revenue) revenue, COUNT(DISTINCT user_id) users,
 AVG(support_tickets) avg_tickets
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-14'
GROUP BY cohort;

Reason: verifies breadth of impact and which cohorts moved.

2) Attribution and exposure sanity: validate who was targeted/exposed vs control.

sql

SELECT campaign_id, exposure_flag, COUNT(*) users
FROM user_campaign_exposure
GROUP BY 1,2;

Reason: rule out mis-tagging or overlap with other campaigns.

3) New-user quality: compare activation funnels and LTV proxies for new users from campaign.- Funnel conversion rates (signup→day7-active→purchase)- Avg order value, return rate.

4) Support ticket dive: sample tickets for campaign-attributed users; classify by topic using keyword tagging/NLP to see common issues.

python

# pseudo
from sklearn.feature_extraction.text import TfidfVectorizer
# cluster or label topics to find spikes

5) Product telemetry & client-side logs: check error/exception rates, page load times, checkout failures correlated with campaign time windows and user segments.

6) Channel & creative analysis: break down by source/creative/ad-copy — compute revenue per user vs support rate per creative to spot problematic creative driving low-quality users.

7) Fraud/abuse signals: check abnormal device fingerprints, rapid signups, proxy IPs, conversion rates by geo/time to exclude bots or incentive abuse.

8) Quick experiments / mitigations: run rapid A/B test or rollback for highest-risk creative or audience; or throttle campaign for a subset and monitor ticket/revenue delta.

For each step I’d log findings, prioritize actions by expected impact × cost, and communicate early hypotheses + next analyses to product/marketing/support.

Unlock Full Question Bank

Get access to hundreds of Metric Diagnosis & Segmentation Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.