Rates: a numerator, a denominator, and the same grain for both

A rate is two counts pretending to be one number

Conversion rate, refund rate, pass rate, open rate: each is a numerator divided by a denominator, and every way a rate goes wrong is a way one of the two halves was computed differently from the other. This lesson is about the mechanics; the earlier lesson on right versus useful numbers made the point that the denominator is where the meaning lives.

The division that stops the query

Postgres refuses to divide by zero and aborts the whole query with ERROR: division by zero. MySQL, SQLite and DuckDB return NULL for the same expression and carry on. So a rate query that runs on your laptop’s DuckDB can fail in production Postgres on the first day a branch has no visits. Guard the denominator explicitly:

SELECT branch,
       count(*) FILTER (WHERE converted)                       AS conversions,
       count(*)                                                AS visits,
       count(*) FILTER (WHERE converted)::numeric
         / nullif(count(*), 0)                                 AS rate
FROM visits
GROUP BY branch;

nullif(x, 0) returns NULL when x is zero, so the division yields NULL instead of an error. NULL is the honest result: a branch with no visits does not have a conversion rate of zero, it has no conversion rate. Note the ::numeric cast, which the lesson on averages explained; without it two integer counts divide to 0.

The average of rates is not the rate

You have a daily table with a rate column. Last month’s rate is not avg(rate). Sunday had 12 visits and 6 conversions, a rate of 50%. Monday had 4,000 visits and 120 conversions, a rate of 3%. The average of the two rates is 26.5%. The actual rate across both days is 126 out of 4,012, which is 3.1%. Days with almost no traffic swing an average of rates violently and barely move the true rate.

The rule is the same as for averages: keep the numerator and denominator as separate columns, sum each, and divide the sums.

SELECT sum(conversions)::numeric / nullif(sum(visits), 0) AS pooled_rate
FROM daily_stats
WHERE day >= '2026-03-01' AND day < '2026-04-01';

Both halves must be drawn from the same pile

The most common rate bug is a numerator from one query and a denominator from another, each with its own filters. Conversions counted in local time and visits in UTC. Conversions including test accounts and visits excluding them. Orders placed in March over visitors seen in March, where a visitor arriving on 31 March and buying on 1 April is in the denominator but not the numerator. Each mismatch moves the rate by a few percent, which is exactly the size of change somebody will present as a result.

The defence is to compute both halves in one query from one filtered set, as the first example does. When the two halves come from different tables, join them at the same grain first and check that each side has the same row count you expect, then divide.

The trick with booleans

When the numerator is “rows where something is true”, the rate is the average of a 0/1 column:

SELECT avg(CASE WHEN converted THEN 1 ELSE 0 END) AS rate FROM visits;
-- DuckDB and Postgres also accept avg(converted::int)

It is compact, it is portable, and it fails safely on an empty group by returning NULL.

Percentage change has its own trap

“Up 40% on last month” is (this - last) / last. When last is zero the expression divides by zero; when last is negative, as a net figure can be, the sign flips and “up” becomes “down”. Guard with nullif and, for figures that can be negative, report the absolute change alongside the percentage or instead of it.

Small denominators

One out of two is 50%. It is also meaningless. Any rate reported without its denominator invites the reader to trust a coin flip as much as a census. Put n in the next column, always, and consider hiding rates where n is under some floor, say 30, or marking them. A grouped report in which the highest and lowest rates both belong to tiny groups is the normal case, not the exception, because small groups have the most room to land at the extremes by chance.

The habit

Write the numerator and the denominator as two columns before writing the division. Look at both. Then divide, guarded, with the count beside the result. A rate that arrives with its two parts can be argued with; a rate that arrives alone cannot.