Lift, Significance and Guardrails in SQL
Computing conversion lift and the data checks that flag a broken experiment.
Lift, Significance and Guardrails in SQL is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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;Absolute vs Relative Lift
Two definitions of lift, and interviewers want the relative one by default:
- Absolute lift = p_treatment - p_control (percentage points).
- Relative lift = (p_treatment - p_control) / p_control (a percent improvement).
Saying "a 2 point increase" vs "a 20% relative lift" can describe the same result. Be explicit.
Computing Lift With a Self-Pivot
To compare two variants in one row, pull control and treatment side by side using conditional aggregation, then do the arithmetic.
This avoids a fragile self-join and keeps the lift formula readable.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
),
rates AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p_ctrl,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p_trt
FROM s
)
SELECT
p_ctrl, p_trt,
p_trt - p_ctrl AS abs_lift,
ROUND(100.0 * (p_trt - p_ctrl) / p_ctrl, 2) AS rel_lift_pct
FROM rates;Why a Difference Might Be Noise
A higher treatment rate could be random sampling luck. Significance asks: how likely is a gap this large if the variants were truly identical?
The key ingredient is the standard error of each rate, which shrinks as sample size grows. Big samples make small lifts trustworthy; tiny samples make even large lifts suspect.
Standard Error of a Proportion
For a conversion rate p over n users, the standard error is sqrt(p * (1 - p) / n). Compute it per variant directly in SQL.
This quantifies the wobble in each rate before you compare them.
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
)
SELECT
variant, n,
1.0 * c / n AS p,
SQRT( (1.0*c/n) * (1 - 1.0*c/n) / n ) AS std_err
FROM s;A Z-Score for Two Proportions
A rough significance signal is the two-proportion z-score: the difference in rates divided by the standard error of that difference. A magnitude above about 1.96 corresponds to the common 95% threshold.
State clearly this is an approximation, not a substitute for a proper test, but it answers "is this plausibly real?" in SQL.
WITH r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p1,
MAX(CASE WHEN variant='control' THEN n END) AS n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p2,
MAX(CASE WHEN variant='treatment' THEN n END) AS n2
FROM (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
) s
)
SELECT
p2 - p1 AS abs_lift,
(p2 - p1) / SQRT( p1*(1-p1)/n1 + p2*(1-p2)/n2 ) AS z_score
FROM r;Interpreting the Z-Score
Translate the number into a verdict so the interviewer hears business sense, not just math:
|z| >= 1.96: difference is significant at roughly 95% confidence.|z| < 1.96: not enough evidence; the lift may be noise.
Wrap it in a CASE to emit a readable label, and always pair significance with the practical size of the lift.
SELECT
z_score,
CASE WHEN ABS(z_score) >= 1.96
THEN 'significant at 95%'
ELSE 'not significant' END AS verdict
FROM (
SELECT 2.3 AS z_score
) t;Sample Ratio Mismatch (SRM)
The first guardrail interviewers probe: did users actually split as designed? A 50/50 experiment that lands 53/47 with millions of users is a red flag, the randomization or logging is broken.
Compare observed counts against the expected split. A large deviation invalidates the whole test before you even read the metric.
WITH cnt AS (
SELECT variant, COUNT(DISTINCT user_id) AS n
FROM experiment_flat GROUP BY variant
),
tot AS (SELECT SUM(n) AS total FROM cnt)
SELECT
c.variant, c.n,
ROUND(100.0 * c.n / t.total, 2) AS observed_pct,
50.0 AS expected_pct
FROM cnt c CROSS JOIN tot t;Guardrail Metrics
A guardrail is a metric that must not get worse even if the primary metric improves. Classic guardrails: page latency, refund rate, unsubscribe rate, error rate.
Report them per variant alongside the win metric. A treatment that lifts conversion but doubles refunds is not a win. Computing guardrails unprompted signals product judgment.
SELECT
variant,
AVG(load_ms) AS avg_latency_ms,
ROUND(100.0 * SUM(refunded) / COUNT(*), 2) AS refund_rate_pct,
ROUND(100.0 * SUM(errored) / COUNT(*), 2) AS error_rate_pct
FROM experiment_flat
GROUP BY variant;The Full Readout
A complete experiment readout interviewers love combines four things in one result: per-variant rates, relative lift, the significance verdict, and the SRM check. Layer CTEs and present it as a single decision-ready table.
Close by stating: significant, lift size acceptable, guardrails healthy, split balanced, therefore ship or hold.
WITH s AS (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
),
r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) p1,
MAX(CASE WHEN variant='control' THEN n END) n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) p2,
MAX(CASE WHEN variant='treatment' THEN n END) n2
FROM s
)
SELECT
ROUND(100.0*(p2-p1)/p1, 2) AS rel_lift_pct,
CASE WHEN ABS((p2-p1)/SQRT(p1*(1-p1)/n1 + p2*(1-p2)/n2)) >= 1.96
THEN 'significant' ELSE 'not significant' END AS verdict,
CASE WHEN ABS(1.0*n2/(n1+n2) - 0.5) > 0.02
THEN 'SRM warning' ELSE 'split ok' END AS srm_check
FROM r;Quick Check
Treatment shows a 25% relative lift in conversion, but each variant has only 40 users. What is the right conclusion?
Recap: Lift, Significance and Guardrails
You can now turn raw variant metrics into a decision:
- Distinguish absolute (point) lift from relative (percent) lift.
- Compute the standard error of each rate and a two-proportion z-score as a rough significance signal (|z| >= 1.96 ~ 95%).
- Run the SRM check to confirm the split matches the design.
- Report guardrail metrics so a win does not hide a regression.
- Present a single decision-ready readout and always pair significance with practical lift size.
That completes funnel and A/B test analysis in SQL.
Frequently asked questions
Is the “Lift, Significance and Guardrails in SQL” lesson free?
Yes — the full text of “Lift, Significance and Guardrails in SQL” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Lift, Significance and Guardrails in SQL”?
Computing conversion lift and the data checks that flag a broken experiment. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Lift, Significance and Guardrails in SQL” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
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