SQL for Analytics
Advanced 14 min read SQL for Analytics

SQL for A/B Testing

Answering 'did the variant beat control?' in SQL

Experiment analysis in SQL: computing per-user metrics by variant, comparing them, and checking the results flagged by significance tests — all while avoiding pre-aggregation mistakes.

A/B testingexperiment analysisvariantconversionuser-level metricsSQL
Prerequisites: SQL Aggregations Statistics: Hypothesis Testing
Before we start

What you will learn

  • Compute user-level metrics per variant in SQL
  • Avoid pre-aggregation and bias traps in experiment queries
  • Combine SQL results with significance test interpretation
Introduction

What is this?

SQL for A/B testing computes per-variant summaries from experiment assignment and outcome data: who was enrolled in which variant, then their metric (conversion, revenue, duration) aggregated at the user level, then compared. The discipline is computing the metric once per user — not per event — then aggregating per variant, and knowing when a diff is significant.

Motivation

Why do you need it?

  • Experiments are the fastest way to claim causality; SQL powers the measurement.
  • User-level aggregation prevents a few heavy users from skewing numbers.
  • Dashboards for experiments need exact, reproducible queries.
  • Significance math on top of solid SQL avoids 'we saw a 2% lift' noise.
Think about it

Mental model

Imagine

Scorecards for two teams

Think of

Per-variant aggregates

You don't total up every play (event); you record each player's season (user metric), then compare team averages. SQL's job is keeping the scoring consistent so the comparison is fair.

Mechanics

How it works

  • Experiment enrollment table assigns user_id → variant (control/test).
  • Outcomes table holds user actions; compute per-user metrics first (CTE or subquery).
  • Join enrollment to user-metric CTE, then GROUP BY variant for means and counts.
  • Compute sample sizes and, later, the t/z-statistic against the two groups.
  • Guard against overlap: users enrolled in other simultaneous experiments taint the comparison.

From enrollment to variant summary.

Enrollment
Outcomes
User metric CTE
Join + GROUP BY
Compare
Hands-on

Examples

Experiment analysis skeleton

Experiment analysis skeleton
WITH users AS (
  SELECT user_id, variant
  FROM experiment_assignments
  WHERE experiment_id = 'pricing_v2'
),
metrics AS (
  SELECT user_id,
         COUNT(*) AS sessions,
         MAX(CASE WHEN achieved_goal THEN 1 ELSE 0 END) AS converted
  FROM user_actions
  GROUP BY user_id
)
SELECT e.variant,
       COUNT(*)             AS n_users,
       ROUND(AVG(m.converted), 4) AS conversion,
       ROUND(AVG(m.sessions), 2)  AS avg_sessions
FROM users e
LEFT JOIN metrics m USING (user_id)
GROUP BY e.variant;

Metrics are computed per user FIRST, then joined to assignment and summarized per variant — the user is the unit of randomization and analysis.

Real world

In production

  • A pricing experiment reporting conversion and revenue per user by variant.
  • A feature test using sessions/actions per user to detect side effects.
  • Guardrail checks: control group metrics stay stable week over week.
Guidelines

Best practices

  • Compute metrics at the randomization unit (usually the user).
  • Exclude users enrolled in overlapping experiments or use experiment isolation.
  • Use pre-registered primary and guardrail metrics.
  • Keep the SQL reproducible and version-deployed so results are auditable.
Watch out

Common mistakes

  • Aggregating per event, letting heavy users dominate variant means.
  • Reading a 2% lift as proof without a significance test.
  • Peeking at results daily until 'significant' — p-hacking by iteration.
  • Assignment dates not covering outcome windows so late users corrupt metrics.
When things break

Debugging tips

  • Variant counts wildly unequal? Check a balanced random split and enrollment dates.
  • A user big enough to move the mean? Cap or winsorize outliers or report median too.
  • Conversion rate >1? You converted at the event grain instead of per user.
  • Results unstable day to day? Sample size likely too small — check power.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

In experiment SQL, which is the correct analysis unit?

2

A big p-value in a small sample means the treatment has no effect.

Go deeper

Official documentation

BigQuery experiment stats
docs