Chapter 18 · 13 min · Basic
Significance without a stats library
Three chapters of checks, and the deferred question is still sitting there: the rate moved by 1.6 points. Is that anything?
The honest answer has a shape, and the shape is always the same. Work out how much a difference this size would wobble purely by chance, divide the observed difference by that wobble, and compare the result against a threshold somebody chose in advance. That is the whole of a two-proportion z-test, and it is four arithmetic operations.
Postgres has no normal distribution function, which turns out not to matter. The thresholds are a handful of constants that have not changed since they were printed in the back of textbooks, so they go in a table and you join against them. Storing the critical values as rows makes the threshold visible in the query — which is better than a library call, because the choice of 5% is the part a reader most needs to see.
The dataset
One finished experiment as two rows of totals: 2,000 users per arm, 200 conversions in control and 232 in treatment. z_critical holds the constants — two two-sided significance thresholds and two power values — because Postgres cannot compute them and reading them off a table is what everyone did before it could.
Schema
| variant | text |
| users | int |
| conversions | int |
| purpose | text |
| level | numeric(4,3) |
| z | numeric(5,3) |
Example data
Cast before you divide
Before any statistics, the trap that silently destroys all of it.
conversions and users are both integers, so conversions / users is integer division and returns 0. Not an error, not a null — zero, for both arms, which then flows into a standard error of zero and a z statistic that either divides by zero or comes out absurd.
The column in the middle of this query is there to be looked at once. Every rate, every proportion and every standard error in this chapter starts with a ::numeric cast, and this is why.
It is the same rule as the derived column that encodes an assumption, with a harsher failure mode: a wrong bucket is visible, a silent zero is not. If a proportion comes out as exactly 0 or exactly 1, suspect the cast before you suspect the data.
200 conversions from 2,000 users. What will the integer-division column print?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The pooled rate and the wobble
- 03One division, then a lookup
- 04Not significant is not the same as no effect
- 05The number you needed before you started
- 06Now size a test against real traffic