How to Run A/B Test Analysis with SQL

Here's how most A/B test analysis actually works: someone runs a query, sees 5.2% conversion for the treatment and 4.8% for control, announces "the experiment won," and ships it. Nobody checks whether that difference is real. With 500 users per group, it almost certainly isn't — you'd need a conversion delta roughly 3x that size to clear statistical significance at that sample size. You shipped noise.

Mixpanel and Amplitude have stats engines built in, but they also cost $50k/year and lock your experiment data behind their SDKs. If your experiments table lives in Postgres, you can do the full analysis — significance tests, confidence intervals, power calculations — in SQL. The math isn't hard. The hard part is remembering to do it at all.

I'll use two tables throughout: experiments(user_id, experiment_name, variant, enrolled_at) and conversions(user_id, event_name, revenue, created_at).

Raw conversion rates

Start with what everyone already does, because you need the base numbers before you can test them:

WITH experiment_users AS (
  SELECT
    e.variant,
    COUNT(DISTINCT e.user_id) AS enrolled,
    COUNT(DISTINCT c.user_id) AS converted,
    ROUND(100.0 * COUNT(DISTINCT c.user_id)
      / NULLIF(COUNT(DISTINCT e.user_id), 0), 2) AS conversion_rate
  FROM experiments e
  LEFT JOIN conversions c
    ON c.user_id = e.user_id
    AND c.event_name = 'purchase'
    AND c.created_at >= e.enrolled_at
  WHERE e.experiment_name = 'checkout_redesign_v2'
  GROUP BY e.variant
)
SELECT * FROM experiment_users

The LEFT JOIN matters. An INNER JOIN drops users who never converted, and your denominator shrinks to only people who bought something. Your "conversion rate" becomes 100% for both groups. I've seen this bug in production dashboards at companies that should know better.

Also note c.created_at >= e.enrolled_at — you only want conversions that happened after enrollment. Without that filter, a user who bought something yesterday and got enrolled in an experiment today counts as a conversion. Pre-exposure conversions contaminate both groups and bias your results toward zero difference.

Statistical significance: the z-test

Two conversion rates aren't enough. You need to know if the gap between them is bigger than what random chance would produce. A z-test for two proportions does exactly this: it compares the observed difference against the variance you'd expect from sampling noise. If p < 0.05, there's less than a 5% probability that a difference this large appeared by accident — the treatment likely had a real effect.

WITH stats AS (
  SELECT
    variant,
    COUNT(DISTINCT e.user_id) AS n,
    COUNT(DISTINCT c.user_id) AS conversions
  FROM experiments e
  LEFT JOIN conversions c
    ON c.user_id = e.user_id
    AND c.event_name = 'purchase'
    AND c.created_at >= e.enrolled_at
  WHERE e.experiment_name = 'checkout_redesign_v2'
  GROUP BY variant
),
rates AS (
  SELECT
    MAX(CASE WHEN variant = 'control' THEN conversions::FLOAT / n END) AS p_ctrl,
    MAX(CASE WHEN variant = 'control' THEN n END) AS n_ctrl,
    MAX(CASE WHEN variant = 'treatment' THEN conversions::FLOAT / n END) AS p_treat,
    MAX(CASE WHEN variant = 'treatment' THEN n END) AS n_treat
  FROM stats
),
ztest AS (
  SELECT
    p_ctrl,
    p_treat,
    n_ctrl,
    n_treat,
    p_treat - p_ctrl AS lift,
    (p_ctrl * n_ctrl + p_treat * n_treat) / (n_ctrl + n_treat) AS p_pool,
    (p_treat - p_ctrl)
      / SQRT(
          (p_ctrl * n_ctrl + p_treat * n_treat) / (n_ctrl + n_treat)
          * (1 - (p_ctrl * n_ctrl + p_treat * n_treat) / (n_ctrl + n_treat))
          * (1.0 / n_ctrl + 1.0 / n_treat)
        ) AS z_score
  FROM rates
)
SELECT
  ROUND(p_ctrl * 100, 2) AS control_pct,
  ROUND(p_treat * 100, 2) AS treatment_pct,
  ROUND(lift * 100, 2) AS lift_pct,
  ROUND(z_score, 3) AS z_score,
  CASE
    WHEN ABS(z_score) > 2.576 THEN 'p < 0.01'
    WHEN ABS(z_score) > 1.960 THEN 'p < 0.05'
    WHEN ABS(z_score) > 1.645 THEN 'p < 0.10'
    ELSE 'not significant'
  END AS significance,
  -- 95% confidence interval for the difference
  ROUND((lift - 1.96 * SQRT(p_ctrl * (1 - p_ctrl) / n_ctrl
    + p_treat * (1 - p_treat) / n_treat)) * 100, 2) AS ci_lower_pct,
  ROUND((lift + 1.96 * SQRT(p_ctrl * (1 - p_ctrl) / n_ctrl
    + p_treat * (1 - p_treat) / n_treat)) * 100, 2) AS ci_upper_pct
FROM ztest

The confidence interval at the bottom is the part people skip. A result of "treatment is 1.2pp better, p = 0.03" sounds decisive until you see the 95% CI is [-0.1pp, +2.5pp]. The interval crossing zero means you can't rule out that the treatment is actually worse. CI width tells you how seriously to take your point estimate.

The CASE block on z-score thresholds is a lookup table — |z| > 1.96 corresponds to p < 0.05 for a two-tailed test. SQL doesn't have a normal CDF function, so you approximate with these fixed cutoffs. Good enough for experiment decisions. If you need exact p-values, push the z-score to a Python step or a scheduled notebook.

SRM check: is your randomization broken?

Before you trust any experiment result, check whether the split is even. If you targeted a 50/50 split and got 4,200 control vs 3,800 treatment, something might be wrong — a bug in the assignment logic, a bot crawling only one variant, or a deploy that briefly sent all traffic to control. This is called a Sample Ratio Mismatch, and it invalidates your results regardless of what the p-value says.

WITH counts AS (
  SELECT
    COUNT(*) FILTER (WHERE variant = 'control') AS n_ctrl,
    COUNT(*) FILTER (WHERE variant = 'treatment') AS n_treat,
    COUNT(*) AS n_total
  FROM experiments
  WHERE experiment_name = 'checkout_redesign_v2'
),
srm AS (
  SELECT
    n_ctrl,
    n_treat,
    n_total,
    -- expected 50/50 split
    n_total / 2.0 AS expected,
    -- chi-squared statistic with 1 degree of freedom
    POWER(n_ctrl - n_total / 2.0, 2) / (n_total / 2.0)
      + POWER(n_treat - n_total / 2.0, 2) / (n_total / 2.0) AS chi_sq
  FROM counts
)
SELECT
  n_ctrl,
  n_treat,
  ROUND(100.0 * n_ctrl / n_total, 1) AS ctrl_pct,
  ROUND(chi_sq, 2) AS chi_squared,
  CASE
    WHEN chi_sq > 6.635 THEN 'SRM DETECTED (p < 0.01) -- DO NOT TRUST RESULTS'
    WHEN chi_sq > 3.841 THEN 'SRM WARNING (p < 0.05)'
    ELSE 'split looks clean'
  END AS srm_status
FROM srm

Run this first. If the SRM flag fires, stop analyzing and go find the bug. I've wasted entire afternoons debugging a "surprising" experiment result that turned out to be a broken assignment cookie.

If your split isn't 50/50, adjust the expected values: for a 90/10 split, use n_total * 0.9 and n_total * 0.1 instead of n_total / 2.0.

Revenue per user: beyond binary conversion

Conversion rate tests treat every purchase equally — a $9 order and a $900 order both count as "1." That's fine for signup flows, but for pricing experiments or upsell tests, you care about revenue. Comparing average revenue per enrolled user (not per converter) is the right metric because it captures both "did more people buy" and "did buyers spend more."

WITH revenue_stats AS (
  SELECT
    e.variant,
    COUNT(DISTINCT e.user_id) AS n,
    COALESCE(SUM(c.revenue), 0) AS total_revenue,
    COALESCE(SUM(c.revenue), 0)::FLOAT
      / COUNT(DISTINCT e.user_id) AS avg_revenue_per_user,
    COALESCE(
      STDDEV(COALESCE(c.revenue, 0)),
      0
    ) AS stddev_revenue
  FROM experiments e
  LEFT JOIN conversions c
    ON c.user_id = e.user_id
    AND c.event_name = 'purchase'
    AND c.created_at >= e.enrolled_at
  WHERE e.experiment_name = 'pricing_page_v3'
  GROUP BY e.variant
)
SELECT
  r1.variant AS control,
  r2.variant AS treatment,
  ROUND(r1.avg_revenue_per_user, 2) AS ctrl_arpu,
  ROUND(r2.avg_revenue_per_user, 2) AS treat_arpu,
  ROUND(r2.avg_revenue_per_user - r1.avg_revenue_per_user, 2) AS arpu_diff,
  -- Welch's t-test for unequal variances
  ROUND(
    (r2.avg_revenue_per_user - r1.avg_revenue_per_user)
    / SQRT(POWER(r1.stddev_revenue, 2) / r1.n
         + POWER(r2.stddev_revenue, 2) / r2.n),
    3
  ) AS t_stat,
  -- Minimum detectable effect: days needed at current enrollment rate
  CEIL(
    2 * POWER(
      (r1.stddev_revenue + r2.stddev_revenue) / 2.0
      / NULLIF(ABS(r2.avg_revenue_per_user - r1.avg_revenue_per_user), 0),
      2
    ) * POWER(1.96 + 0.84, 2)  -- 80% power, alpha=0.05
  ) AS min_users_per_group
FROM revenue_stats r1, revenue_stats r2
WHERE r1.variant = 'control'
  AND r2.variant = 'treatment'

That min_users_per_group column at the end answers the question nobody asks early enough: "given the variance I'm seeing, how many users do I actually need?" If the answer is 12,000 and you have 800, stop watching the dashboard. You're weeks away from a trustworthy result. The formula uses the standard power calculation — 80% power at alpha 0.05, which gives the (1.96 + 0.84)^2 constant.

Revenue data is heavy-tailed. A handful of enterprise deals can blow up your variance and make the test inconclusive even with large samples. If STDDEV is 10x the mean, consider log-transforming revenue or capping outliers at the 99th percentile before running the test.

Segmented analysis

An experiment that's flat overall can hide strong effects in subgroups. Maybe the new checkout works great on mobile and terribly on desktop, and they cancel out. Segment your analysis to catch this:

WITH segments AS (
  SELECT
    e.variant,
    CASE
      WHEN e.enrolled_at < u.created_at + INTERVAL '7 days' THEN 'new_user'
      ELSE 'returning_user'
    END AS segment,
    COUNT(DISTINCT e.user_id) AS n,
    COUNT(DISTINCT c.user_id) AS conversions,
    ROUND(100.0 * COUNT(DISTINCT c.user_id)
      / NULLIF(COUNT(DISTINCT e.user_id), 0), 2) AS conversion_rate
  FROM experiments e
  JOIN users u ON u.id = e.user_id
  LEFT JOIN conversions c
    ON c.user_id = e.user_id
    AND c.event_name = 'purchase'
    AND c.created_at >= e.enrolled_at
  WHERE e.experiment_name = 'checkout_redesign_v2'
  GROUP BY e.variant, segment
)
SELECT
  segment,
  MAX(CASE WHEN variant = 'control' THEN n END) AS ctrl_n,
  MAX(CASE WHEN variant = 'control' THEN conversion_rate END) AS ctrl_rate,
  MAX(CASE WHEN variant = 'treatment' THEN n END) AS treat_n,
  MAX(CASE WHEN variant = 'treatment' THEN conversion_rate END) AS treat_rate,
  MAX(CASE WHEN variant = 'treatment' THEN conversion_rate END)
    - MAX(CASE WHEN variant = 'control' THEN conversion_rate END) AS lift
FROM segments
GROUP BY segment
ORDER BY lift DESC

Swap the CASE expression for device type, plan tier, acquisition channel, geography — whatever dimension matters for your product. But be careful: the more segments you test, the more likely you find a "significant" result by chance. Five segments at alpha 0.05 means a 23% chance of at least one false positive. If you're slicing five ways, apply a Bonferroni correction (use p < 0.01 instead of 0.05) or treat segment results as hypotheses to validate in a follow-up experiment, not conclusions.

Putting it into practice

The queries above work in any Postgres or DuckDB console. The problem is nobody runs them. Teams set up an experiment, build a dashboard showing conversion rates per variant, and stare at it for two weeks without ever computing a z-score. Then they call the experiment based on vibes.

In Fastero, paste the z-test query into the SQL editor, save it as a versioned query, and add the output to a dashboard that refreshes daily. The significance column updates automatically. When the experiment is done — meaning you've hit the required sample size, not just gotten bored — the dashboard tells you. Set a Slack alert on the significance field and you don't even need to check.

Pair experiment results with cohort retention to confirm the winning variant actually retains users, not just converts them. A treatment that lifts Day 1 conversion but tanks Week 4 retention is a net loss.

Try Fastero free — run significance tests on your experiment data in SQL, build live dashboards that track results automatically, and stop shipping noise. No credit card required.