SQL for A/B Test Analysis (Step-by-Step)

Step 1 — Check sample ratio mismatch

Before trusting any conversion-rate comparison, confirm users actually landed in each variant at the intended ratio:

SELECT
  variant,
  COUNT(DISTINCT user_id) AS assigned_users,
  ROUND(COUNT(DISTINCT user_id) * 100.0 / SUM(COUNT(DISTINCT user_id)) OVER (), 2) AS pct_of_total
FROM ab_test_assignments
WHERE test_name = 'checkout_button_color'
GROUP BY variant;

Step 2 — Calculate conversion rate per variant

SELECT
  a.variant,
  COUNT(DISTINCT a.user_id) AS assigned_users,
  COUNT(DISTINCT c.user_id) AS converted_users,
  ROUND(COUNT(DISTINCT c.user_id) * 100.0 / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM ab_test_assignments a
LEFT JOIN conversions c
  ON c.user_id = a.user_id AND c.converted_at >= a.assigned_at
WHERE a.test_name = 'checkout_button_color'
GROUP BY a.variant;

Counting DISTINCT users on both sides of the ratio matters — if a user can convert more than once (repeat purchases, for instance), counting raw conversion events instead of unique converting users would overstate the rate for whichever variant happens to have more repeat activity, independent of whether it's actually winning.

Step 3 — Segment the results

SELECT
  a.variant,
  u.device_type,
  COUNT(DISTINCT a.user_id) AS assigned_users,
  ROUND(COUNT(DISTINCT c.user_id) * 100.0 / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM ab_test_assignments a
JOIN users u ON u.user_id = a.user_id
LEFT JOIN conversions c ON c.user_id = a.user_id AND c.converted_at >= a.assigned_at
WHERE a.test_name = 'checkout_button_color'
GROUP BY a.variant, u.device_type;

An overall result can hide two real but opposite effects that cancel out — a change that clearly helps mobile users and does nothing (or even hurts) desktop users can average out to "no significant difference" at the aggregate level. Segmenting is how that gets caught, though segments should generally be chosen before looking at the data, not searched for afterward until one happens to look significant.

Where SQL's job ends

SQL is well suited to producing the summary numbers a significance test needs — sample sizes and conversion counts per variant — but computing an actual p-value or confidence interval from those numbers is a statistics problem, not a SQL one. A two-proportion z-test or chi-square test is the standard tool for a conversion-rate comparison like this, run in a statistics library (Python's scipy.stats, R), a spreadsheet formula, or a dedicated experimentation platform, using the exact assigned/converted counts SQL just produced as its input.

Common mistakes

• Skipping the sample ratio mismatch check and going straight to comparing conversion rates.
• Counting conversion events instead of distinct converting users when a user can convert more than once.
• Peeking at results daily and stopping as soon as one day looks significant — this inflates the false-positive rate well above the intended threshold.
• Searching many segments after the fact for one that looks significant, rather than pre-specifying which segments matter before running the test.
• Treating a SQL-computed percentage difference as proof of significance without running an actual statistical test on the underlying counts.