SQL Experiment Analysis And Data Quality

What's being tested

These problems test practical experiment analysis skills in SQL: writing reliable aggregation queries, validating metric definitions, and spotting data quality issues (duplicates, late events, joins that drop users). Interviewers probe whether you can produce defensible, auditable metrics quickly and reason about edge cases a PM must catch.

Patterns & templates

• Last-event-per-user: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) to dedupe and pick canonical row; handle ties explicitly.

• Unique user counts: COUNT(DISTINCT user_id) for reach; beware cardinality limits in BigQuery/Redshift.

• Exposure → outcome joins: left-join exposure cohort to outcome events, then aggregate conversion rate with SUM(outcome = TRUE)::FLOAT / COUNT(*).

• Time-window cohorts: window by DATE_TRUNC('day', ts) or sliding windows with BETWEEN ts AND ts + INTERVAL '7 days'.

• Metric sanity checks: compute sum(value), count(*), count(distinct id) and compare across data sources for parity.

• Handling late-arriving events: add event_date vs ingest_date filters and document lag policy; compare ingest_date histograms.

• NULLs and defaults: use COALESCE(...) for nullable dimensions; explicitly report NULL as a bucket.

Tip: Push filters before joins and use PARTITION BY/CLUSTER BY in BigQuery/Postgres for large tables.

Common pitfalls

Pitfall: Reporting a conversion rate by joining only successful events (inner join) inflates the denominator—use left join to keep exposed users.

Pitfall: Using COUNT(*) instead of COUNT(DISTINCT user_id) when users generate multiple events double-counts impact.

Pitfall: Ignoring late-arriving events and not stating the reconciliation window causes surprising metric shifts after launch.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.