Chapter 1 built a user-level table — one row per assigned user, with their variant and their aggregated metric. This chapter turns that table into an actual hypothesis test: means, variances, a pooled standard error, and a t-statistic, computed entirely with SQL aggregate functions, with no data ever leaving the warehouse.
What a T-Test Actually Needs
A two-sample t-test compares two group means relative to how much those means would be expected to wobble by chance. Concretely, it needs exactly four numbers per arm: the sample size, the mean, and the variance — combined into a standard error, which the observed difference in means is then divided by:
t = (mean_treatment − mean_control) / SE, where SE = √(variance_control / n_control + variance_treatment / n_treatment)
Every one of those quantities — count, mean, variance — is a standard SQL aggregate function. There's no need for a notebook or a sample pulled out of the warehouse; the whole computation is one query.
The SQL
Starting from the user_metrics table built in Chapter 1 (one row per user, with variant and revenue), the test statistic for control versus the 10%-discount arm is:
with stats as (
select
variant,
count(*) as n,
avg(revenue) as mean_revenue,
var_samp(revenue) as var_revenue
from user_metrics
where variant in ('control', 'discount_10pct')
group by variant
),
pivoted as (
select
max(case when variant = 'control' then n end) as n_c,
max(case when variant = 'control' then mean_revenue end) as mean_c,
max(case when variant = 'control' then var_revenue end) as var_c,
max(case when variant = 'discount_10pct' then n end) as n_t,
max(case when variant = 'discount_10pct' then mean_revenue end) as mean_t,
max(case when variant = 'discount_10pct' then var_revenue end) as var_t
from stats
)
select
mean_t - mean_c as effect,
sqrt(var_c / n_c + var_t / n_t) as standard_error,
(mean_t - mean_c) / sqrt(var_c / n_c + var_t / n_t) as t_stat
from pivoted;var_samp is the standard sample-variance aggregate (dividing by n−1, not n) available in every major warehouse SQL dialect — Snowflake, BigQuery, Redshift, and Postgres all support it directly. The pivoted CTE just reshapes two rows (one per variant) into one row with both arms' stats as columns, so the final division is a single row of arithmetic.
A Small Worked Example
To see exactly what those aggregates are computing, here's a small hand-checkable dataset — five users per arm:
| Control | Treatment |
|---|---|
| 10 | 14 |
| 12 | 16 |
| 8 | 10 |
| 14 | 18 |
| 6 | 12 |
Control's mean is (10+12+8+14+6)/5 = 10; treatment's is (14+16+10+18+12)/5 = 14. The sample variance in each arm — sum of squared deviations from the mean, divided by n−1 = 4 — comes out to exactly 10 in both arms. That makes the standard error √(10/5 + 10/5) = √4 = 2, and the t-statistic (14 − 10) / 2 = 2.0.
With 5 users per arm, there are 8 degrees of freedom, and a t-statistic of 2.0 there falls just short of the conventional 0.05 significance threshold (the two-sided p-value works out to roughly 0.08) — a real-looking effect that a larger sample, or a variance-reduction technique like the ones in Chapters 3 and 6, would be needed to confirm more decisively.
Why This Doesn't Need a Sample Pulled Into Python
The instinct to export a sample and run scipy.stats.ttest_ind comes from most statistics material being written against data that already fits in memory. A production experiment can span tens of millions of users — the aggregation genuinely needs to happen in the warehouse, not after extracting rows. The four aggregate values (count, mean, variance, per arm) are the only sufficient statistics a t-test ever needs; once a SQL query has computed them, the rest is arithmetic on a single row, not a statistical operation that requires row-level data at all.
What This Doesn't Handle Yet
This chapter's t-test assumes the metric is a simple per-user sum, like total revenue. Plenty of real metrics are ratios instead — conversion rate, orders per session, click- through rate — and averaging per-user ratios directly gives the wrong variance, and often the wrong point estimate too. That's the subject of the next chapter.