How to analyze an A/B test with SQL

To analyze an A/B test with SQL, you need three things: one row per user per variant, a clear conversion definition, and a significance check. Count exposed users and conversions per variant, compare the conversion rates to get the lift, then run a two-proportion z-test to decide whether the difference is real or just noise. If the absolute z-score is above 1.96, the result is significant at the 95% confidence level. You do not need a dedicated experimentation platform to do this, but you do need to fix your sample size in advance and resist stopping the test the moment it looks like a winner.

This guide is for product managers, growth engineers, and analysts who have experiment data sitting in Postgres, BigQuery, or a warehouse and want to read the result themselves instead of waiting on a stats tool or a data scientist. It covers the exact SQL to get conversion rates and lift, how to check significance without Python or R, how to think about sample size, the mistakes that quietly turn noise into a “win,” and when a BI tool is enough versus when you should reach for a purpose-built experimentation platform.

What data you need before you start

Every A/B test analysis rests on two facts per user: which variant they saw, and whether they did the thing you care about. In warehouse terms that usually means two tables:

• An assignment table, one row per user per exposure, with a variant column (control or treatment) and a timestamp. This is the source of truth for who was in the test.
• A conversion event, either a dedicated events table or a status column, that tells you whether each user completed the goal (signed up, upgraded, placed an order) after being assigned.

The single most important rule is that the denominator is exposed users, not total users and not events. You measure the conversion rate among people who actually entered the experiment, and you count each user once. If you divide conversions by the wrong population, every number after that is wrong no matter how careful the statistics are.

Two data-quality checks belong here, before any math:

1. No user in both variants. If a user shows up as both control and treatment, your assignment logic leaked and those users have to be dropped or the test is invalid.
2. Assignment happened before conversion. Only count conversions that occurred after the user was exposed. Crediting a purchase that happened before the user ever saw the new checkout is a classic way to invent a lift.

Step 1: Get one clean row per user per variant

Users often have multiple assignment rows (repeated page loads, retries). Collapse them to a single exposure, keeping the first one, and flag anyone who was assigned to more than one variant so you can exclude them.

with assignments as (
  select
    user_id,
    variant,
    row_number() over (partition by user_id order by assigned_at) as rn,
    count(distinct variant) over (partition by user_id)          as variant_count
  from experiment_assignments
  where experiment_key = 'checkout_v2'
)
select user_id, variant
from assignments
where rn = 1
  and variant_count = 1   -- drop users who landed in both arms

This CTE (exposed) is the population for the rest of the analysis. Everything downstream joins back to it.

Step 2: Calculate conversion rate and lift per variant

Now join exposed users to conversions and aggregate. Use a left join so non-converters stay in the denominator, and dedupe conversions to one per user.

with exposed as (
  -- the CTE from step 1
),
converted as (
  select distinct user_id
  from conversions
  where converted_at >= timestamp '2026-08-01'
)
select
  e.variant,
  count(*)                                              as users,
  count(c.user_id)                                      as conversions,
  round(100.0 * count(c.user_id) / count(*), 2)         as conversion_rate_pct
from exposed e
left join converted c on c.user_id = e.user_id
group by e.variant
order by e.variant;

You now have something like:

The relative lift is (treatment_rate - control_rate) / control_rate. Here that is (9.00 - 8.00) / 8.00 = 12.5%. The absolute lift is one percentage point. Report both. A 12.5% relative lift sounds impressive, but if the absolute change is 0.1 points on a metric that barely moves the business, the framing matters.

Step 3: Check whether the difference is real

A different conversion rate is not the same as a better variant. With 16,000 users split evenly, an eight-versus-nine-percent gap could easily be luck. The standard test for comparing two conversion rates is the two-proportion z-test. You can compute it in plain SQL.

with variant_results as (
  -- the query from step 2, aliased so we can pivot it
  select variant, users, conversions from ab_results
),
stats as (
  select
    max(case when variant = 'control'   then users end)       as n_c,
    max(case when variant = 'control'   then conversions end) as x_c,
    max(case when variant = 'treatment' then users end)       as n_t,
    max(case when variant = 'treatment' then conversions end) as x_t
  from variant_results
)
select
  x_c::float / n_c as p_control,
  x_t::float / n_t as p_treatment,
  (x_t::float / n_t - x_c::float / n_c)
    / sqrt(
        ((x_c + x_t)::float / (n_c + n_t))
        * (1 - (x_c + x_t)::float / (n_c + n_t))
        * (1.0 / n_c + 1.0 / n_t)
      ) as z_score
from stats;

Read the z-score with two thresholds:

• |z| ≥ 1.96 means the result is significant at the 95% confidence level (p < 0.05).
• |z| ≥ 2.58 means significant at the 99% level (p < 0.01).

For the numbers above the z-score is about 2.2, so treatment beats control at the 95% level. If z had come back at 1.1, you would not have enough evidence to call it, no matter how much you like the new design.

Two caveats. This z-test is built for a binary metric (converted or not) with a reasonably large sample. For a continuous metric like revenue per user, use a t-test or a bootstrap instead, because a few big spenders can distort a mean in ways proportions never show. And a z-score is not a substitute for setting the sample size in advance, which is the next step.

How big a sample do you need?

Significance depends on three inputs you decide before the test starts: your baseline conversion rate, the minimum detectable effect (MDE, the smallest lift worth caring about), and the statistical power (conventionally 80%) and significance level (conventionally 95%). Smaller effects and lower baseline rates need dramatically more traffic.

The practical consequence: you cannot pick a sample size after looking at the data. Decide up front how many users per arm you need to detect your MDE, run until you hit it, then analyze once. Evan Miller’s sample size calculator is a reliable, widely used way to get that number without doing the power calculation by hand. If the required sample is larger than the traffic you can realistically gather in a few weeks, that is a signal the test is not worth running, not a reason to squint at an underpowered result.

Common mistakes that turn noise into a “win”

Most bad A/B calls come from process errors, not arithmetic. Watch for these:

• Peeking and early stopping. Checking the test daily and stopping the first time it crosses significance massively inflates false positives. A test that is “significant” on day 3 is often back to noise by day 10. Evan Miller’s write-up on this explains why. Either fix the sample size and analyze once, or use a method designed for continuous monitoring.
• Sample ratio mismatch (SRM). If you intended a 50/50 split but see 8,000 versus 7,200 users, assignment is broken and the whole comparison is suspect. Check the ratio first; a lopsided split is a red flag, not a rounding artifact.
• Testing many metrics at once. Look at ten metrics and one will likely cross p < 0.05 by chance alone. Name a single primary metric before the test, and treat everything else as directional.
• Ignoring practical significance. With a huge sample, a 0.05-point lift can be statistically significant and still not worth the engineering cost to ship. Statistical significance answers “is it real,” not “is it worth it.”
• Post-hoc segmentation. Slicing a flat result until some segment “wins” (mobile users in Canada on Tuesdays) is how you ship random noise. Pre-register the segments you plan to look at.
• Short runtimes that miss weekly cycles. Behavior differs by weekday. Run for full weeks so weekend and weekday users are represented in both arms.

When not to run an A/B test

Experiments are not free, and some decisions do not benefit from one:

• Low traffic. If reaching your MDE would take months, the market will change before the test finishes. Ship based on judgment or qualitative research instead.
• Obvious fixes. You do not need a test to fix a broken button or a crash.
• One-way-door decisions. Rebrands, pricing overhauls, or contractual changes are hard to reverse and often can’t be cleanly split. Use other evidence.
• Changes you will ship regardless. If leadership has already decided, an A/B test is theater. Skip it and measure the rollout instead.

A checklist before you trust a result

Run through this before you write “treatment wins” in a doc:

1. Sample size was fixed before the test started.
2. The split ratio matches what you intended (no SRM).
3. Each user appears once, in exactly one variant.
4. Only post-exposure conversions are counted.
5. There is one pre-declared primary metric.
6. The test ran for full weekly cycles.
7. The z-score (or equivalent) clears your confidence threshold.
8. The absolute lift is large enough to be worth shipping.

If any item fails, the honest answer is “we don’t know yet,” not “close enough.”

BI tool and SQL, or a dedicated experimentation platform?

Analyzing a test in SQL is the right call when you run a handful of experiments, already trust your warehouse, and want full control over how metrics are defined. A dedicated experimentation platform earns its keep once you are running many concurrent tests and need randomized assignment, automatic significance, and guardrail metrics handled for you.

If your experiment data lives in a production database or warehouse, a modern BI tool is often enough to run the full analysis. Basedash connects to Postgres, MySQL, Snowflake, and BigQuery, and lets you write the queries above or ask for the same numbers in plain English, then share the result as a dashboard the whole team can read. That keeps experiment analysis next to the rest of your product analytics instead of in a separate silo.

Frequently asked questions

Can I calculate statistical significance in SQL without Python or R?

Yes. For a binary conversion metric, the two-proportion z-test is simple arithmetic you can express directly in SQL, as shown above. Compare the absolute z-score against 1.96 for 95% confidence or 2.58 for 99%. For continuous metrics like revenue per user, SQL alone is weaker, and you are better off exporting to a stats library or a purpose-built tool that can run a t-test or bootstrap.

What sample size do I need for an A/B test?

It depends on your baseline conversion rate, the minimum lift you want to detect, and your target power and confidence. Lower baselines and smaller effects need much more traffic. Decide the number before the test using a power calculation or a sample size calculator, then run until you hit it. Choosing a sample size after seeing the data invalidates the significance test.

Why shouldn’t I stop a test as soon as it’s significant?

Because checking repeatedly and stopping at the first significant reading inflates your false positive rate far beyond the 5% you think you are accepting. Results that look significant early often revert to noise. Fix the sample size and analyze once, or use a sequential testing method built for continuous monitoring.

How do I calculate lift between two variants?

Relative lift is (treatment_rate - control_rate) / control_rate. Absolute lift is the raw difference in percentage points. A one-point move from 8% to 9% is a 12.5% relative lift but a one-point absolute lift. Report both, since relative numbers can make small absolute changes sound larger than they are.

What is sample ratio mismatch and why does it matter?

Sample ratio mismatch (SRM) is when the actual split between variants differs meaningfully from what you configured, for example 53/47 when you intended 50/50. It usually means assignment, logging, or filtering is broken, which biases the comparison in ways the significance test cannot fix. Check the ratio first; if it fails, debug the pipeline before reading any results.

Do I need an experimentation platform, or is a BI tool enough?

If you run occasional tests and your data is in a warehouse, a BI tool and SQL can handle the full analysis with full control over metric definitions. If you run many concurrent experiments and need randomized assignment, automatic significance, guardrail metrics, and continuous monitoring handled for you, a dedicated experimentation platform is worth the cost. Many teams start in SQL and adopt a platform only when experiment volume grows.