Lift, Significance and Guardrails in SQL
Computing conversion lift and the data checks that flag a broken experiment.
From Metrics to a Decision
Per-variant conversion is only the start. The interview question is: did treatment actually win? That means computing lift, gauging whether the difference is real or noise, and checking guardrail metrics that catch a broken experiment.
You will not run a full statistics package in SQL, but you can compute the inputs and a rough significance signal interviewers want to see.
The Per-Variant Summary CTE
Everything downstream builds on one tidy summary: per variant, the user count n, the converter count c, and the conversion rate p. Compute it once in a CTE and reuse it.
WITH summary AS (
SELECT
variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat
GROUP BY variant
)
SELECT
variant, n, c,
1.0 * c / n AS p
FROM summary;All lessons in this course
- Building a Multi-Step Funnel
- Ordered Events and Time Windows
- A/B Test Assignment and Metrics
- Lift, Significance and Guardrails in SQL