A/B Test Analysis with SQL

Why A/B Test Analysis Is a Common Interview and Job Topic

Nearly every consumer product company runs experiments, and the SQL to analyze them — computing conversion rates per variant, checking for statistical significance, and watching for common pitfalls like sample ratio mismatch — is a realistic, high-value skill for data analyst and data scientist roles. This guide covers the query patterns, plus the statistical reasoning needed to interpret them correctly.

The Sample Data

-- experiment_assignments: which variant each user was assigned to
| user_id | experiment_id | variant  | assigned_at          |
|---------|-----------------|-----------|------------------------|
| 1       | checkout_v2     | control   | 2026-01-01 09:00:00   |
| 2       | checkout_v2     | treatment | 2026-01-01 09:05:00   |

-- conversions: whether/when an assigned user converted
| user_id | converted_at         |
|---------|------------------------|
| 2       | 2026-01-01 09:30:00   |

Step 1: Compute Conversion Rate Per Variant

SELECT
  a.variant,
  COUNT(DISTINCT a.user_id) AS users_assigned,
  COUNT(DISTINCT c.user_id) AS users_converted,
  ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM experiment_assignments a
LEFT JOIN conversions c ON a.user_id = c.user_id
WHERE a.experiment_id = 'checkout_v2'
GROUP BY a.variant;

Use LEFT JOIN, not INNER JOIN — an INNER JOIN would silently exclude every user who was assigned but never converted, which corrupts the denominator entirely (it would look like 100% conversion, counting only converters).

Step 2: Check for Sample Ratio Mismatch

Before trusting any result, verify users were actually split as intended (e.g. a planned 50/50 split). A skewed split is a strong signal something is wrong with the assignment mechanism itself, and any conclusion drawn from a mismatched experiment is unreliable regardless of the conversion numbers.

SELECT variant, COUNT(DISTINCT user_id) AS n,
  ROUND(100.0 * COUNT(DISTINCT user_id) / SUM(COUNT(DISTINCT user_id)) OVER (), 2) AS pct_of_total
FROM experiment_assignments
WHERE experiment_id = 'checkout_v2'
GROUP BY variant;
-- If this isn't close to the intended split (e.g. 50/50), investigate before analyzing results

Step 3: Statistical Significance — the Z-Test for Proportions

A/B test conversion rates are proportions, and the standard test for comparing two proportions is a two-proportion z-test. SQL isn't a statistics engine, but the components — sample sizes, conversion counts, pooled proportion, and standard error — can all be computed directly, then the z-score interpreted against a standard normal distribution.

WITH stats AS (
  SELECT
    a.variant,
    COUNT(DISTINCT a.user_id) AS n,
    COUNT(DISTINCT c.user_id) AS conversions
  FROM experiment_assignments a
  LEFT JOIN conversions c ON a.user_id = c.user_id
  WHERE a.experiment_id = 'checkout_v2'
  GROUP BY a.variant
),
pivoted AS (
  SELECT
    MAX(CASE WHEN variant = 'control' THEN n END) AS n_control,
    MAX(CASE WHEN variant = 'control' THEN conversions END) AS conv_control,
    MAX(CASE WHEN variant = 'treatment' THEN n END) AS n_treatment,
    MAX(CASE WHEN variant = 'treatment' THEN conversions END) AS conv_treatment
  FROM stats
)
SELECT
  conv_control::float / n_control AS p_control,
  conv_treatment::float / n_treatment AS p_treatment,
  (conv_control + conv_treatment)::float / (n_control + n_treatment) AS p_pooled,
  (conv_treatment::float / n_treatment - conv_control::float / n_control) /
    SQRT(
      ((conv_control + conv_treatment)::float / (n_control + n_treatment)) *
      (1 - (conv_control + conv_treatment)::float / (n_control + n_treatment)) *
      (1.0 / n_control + 1.0 / n_treatment)
    ) AS z_score
FROM pivoted;
-- A z-score beyond ±1.96 corresponds to statistical significance at the standard 95% confidence level

In practice, most teams compute the raw inputs (sample sizes, conversion counts, rates) in SQL exactly as above, then feed them into a statistics library (Python's scipy.stats, R) or a dedicated experimentation platform for the actual significance test and confidence interval — reserve doing the full z-test arithmetic by hand in raw SQL for interview settings or when no other tool is available.

Segmenting Results by User Attribute

SELECT
  a.variant,
  u.platform,
  COUNT(DISTINCT a.user_id) AS n,
  COUNT(DISTINCT c.user_id) AS conversions,
  ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM experiment_assignments a
LEFT JOIN conversions c ON a.user_id = c.user_id
JOIN users u ON a.user_id = u.id
WHERE a.experiment_id = 'checkout_v2'
GROUP BY a.variant, u.platform;

Caution: segmenting results into many small subgroups after seeing the overall result (rather than as a pre-registered hypothesis) increases the risk of finding a "significant" result purely by chance — this is a well-known statistical pitfall (sometimes called p-hacking or the multiple comparisons problem), not a SQL problem, but the SQL makes it dangerously easy to do without thinking about it.

Novelty Effects: Checking Results Over Time

SELECT
  a.variant,
  DATE_TRUNC('day', a.assigned_at) AS cohort_day,
  COUNT(DISTINCT a.user_id) AS n,
  ROUND(100.0 * COUNT(DISTINCT c.user_id) / COUNT(DISTINCT a.user_id), 2) AS conversion_rate_pct
FROM experiment_assignments a
LEFT JOIN conversions c ON a.user_id = c.user_id
WHERE a.experiment_id = 'checkout_v2'
GROUP BY a.variant, DATE_TRUNC('day', a.assigned_at)
ORDER BY cohort_day, a.variant;

Plotting conversion rate by day (or by cohort assignment day) helps catch novelty effects — a treatment that looks strong initially simply because it's new, with the effect fading over subsequent weeks — something a single aggregate number across the whole experiment period would hide entirely.

Common Mistakes

• Using INNER JOIN instead of LEFT JOIN between assignments and conversions — silently excludes non-converters and corrupts the conversion rate denominator.
• Not checking sample ratio mismatch first — analyzing results from an experiment where the split itself is broken produces meaningless conclusions regardless of the numbers.
• Peeking and stopping early once a result looks significant, rather than committing to a pre-determined sample size or duration — this inflates false-positive rates significantly (a phenomenon sometimes called "peeking").
• Slicing results into many post-hoc segments looking for any significant subgroup — dramatically increases the chance of a false positive purely by chance, as discussed above.

Common Interview Questions

1. Write a query to compute conversion rate by experiment variant. The LEFT JOIN pattern in Step 1 above.
2. Why is LEFT JOIN important here instead of INNER JOIN? To correctly include assigned-but-not-converted users in the denominator.
3. What would you check before trusting an A/B test's result? Sample ratio mismatch, adequate sample size/duration, and whether the metric was pre-registered rather than found through post-hoc segmentation.
4. What's a novelty effect, and how would you detect it in SQL? A treatment effect that fades over time as it stops being new — detected by plotting the metric over the experiment's duration rather than looking only at the final aggregate.

Frequently Asked Questions

Should SQL be used to run the actual statistical significance test?

SQL is well suited to computing the raw inputs (counts, rates), but the significance test itself is usually better handled by a statistics library or dedicated experimentation platform, which correctly handles edge cases, multiple testing correction, and confidence interval calculation that are easy to get subtly wrong in hand-written SQL.

What's the difference between statistical significance and practical significance?

A result can be statistically significant (unlikely to be due to random chance) while still being too small to matter for the business (a 0.1% lift on a low-value metric, for example) — always evaluate both the p-value/confidence interval and the actual effect size.

Practice A/B Test Analysis

Experiment analysis blends SQL fluency with statistical reasoning — a combination interviewers specifically probe for in analytics roles. Try related patterns hands-on with our SQL practice questions, or work through a full experiment case study in our case studies. For the funnel and cohort patterns often paired with experiment analysis, see our product analytics SQL guide.