{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"ratios-and-rates","title":"Rates: a numerator, a denominator, and the same grain for both","url":"https://addaly.com/learn/data-and-sql/ratios-and-rates","position":28,"of":66,"minutes":9,"answer":"Compute a rate's numerator and denominator in one query from one filtered pile, sum each before dividing, guard the division with NULLIF, and show the denominator beside the result.","keyIdea":"Compute a rate's numerator and denominator in one query from one filtered pile, sum each before dividing, guard the division with NULLIF, and show the denominator beside the result."},"body":{"format":"markdown","text":"## A rate is two counts pretending to be one number\n\nConversion 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.\n\n## The division that stops the query\n\nPostgres 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:\n\n```sql\nSELECT branch,\n       count(*) FILTER (WHERE converted)                       AS conversions,\n       count(*)                                                AS visits,\n       count(*) FILTER (WHERE converted)::numeric\n         / nullif(count(*), 0)                                 AS rate\nFROM visits\nGROUP BY branch;\n```\n\n`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`.\n\n## The average of rates is not the rate\n\nYou 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.\n\nThe rule is the same as for averages: keep the numerator and denominator as separate columns, sum each, and divide the sums.\n\n```sql\nSELECT sum(conversions)::numeric / nullif(sum(visits), 0) AS pooled_rate\nFROM daily_stats\nWHERE day >= '2026-03-01' AND day < '2026-04-01';\n```\n\n## Both halves must be drawn from the same pile\n\nThe 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.\n\nThe 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.\n\n## The trick with booleans\n\nWhen the numerator is \"rows where something is true\", the rate is the average of a 0/1 column:\n\n```sql\nSELECT avg(CASE WHEN converted THEN 1 ELSE 0 END) AS rate FROM visits;\n-- DuckDB and Postgres also accept avg(converted::int)\n```\n\nIt is compact, it is portable, and it fails safely on an empty group by returning NULL.\n\n## Percentage change has its own trap\n\n\"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.\n\n## Small denominators\n\nOne 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.\n\n## The habit\n\nWrite 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."},"check":{"question":"A weekly report computes conversion rate as `avg(daily_rate)` from a table with one row per day. Traffic is heavy on weekdays and very light at weekends. Compared with the true weekly rate, what does the report show?","options":[{"text":"The same value, since every day contributes equally to a week.","correct":false,"tempting":"Days contribute equally to the average of rates precisely because their traffic is ignored, which is the error."},{"text":"A value that is too low, because weekend days with few visits post a rate of zero whenever they have no conversions, and two such days drag the seven-day mean down.","correct":false,"tempting":"Assumes the direction of the error; a two-visit day can just as easily post 50% from one sale, and the fault is the equal weighting, not the sign."},{"text":"A value pulled towards the weekend rates, which barely affect the true rate; the fix is to sum conversions and visits separately and divide.","correct":true},{"text":"A value that is too high, because `avg()` skips NULL days and the weekend rows are NULL.","correct":false,"tempting":"Assumes weekend rows are NULL; the scenario says they are light, not missing, and NULL skipping would not explain a rate drawn towards them."}]},"prev":{"slug":"grouping-by-time","title":"Grouping by day, week and month without lying about the boundaries","url":"https://addaly.com/learn/data-and-sql/grouping-by-time"},"next":{"slug":"string-aggregation-and-arrays","title":"Listing the members of a pile: string_agg and array_agg","url":"https://addaly.com/learn/data-and-sql/string-aggregation-and-arrays"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Rates: a numerator, a denominator, and the same grain for both', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/ratios-and-rates (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Rates: a numerator, a denominator, and the same grain for both","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"Compute a rate’s numerator and denominator in one query from one filtered pile, sum each before dividing, guard the division with NULLIF, and show the denominator beside the result.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-rate-is-two-counts-pretending-to-be-one-number","runs":[{"text":"A rate is two counts pretending to be one number"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-division-that-stops-the-query","runs":[{"text":"The division that stops the query"}]},{"t":"p","runs":[{"text":"Postgres refuses to divide by zero and aborts the whole query with "},{"text":"ERROR: division by zero","code":true},{"text":". 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:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT branch,\n       count(*) FILTER (WHERE converted)                       AS conversions,\n       count(*)                                                AS visits,\n       count(*) FILTER (WHERE converted)::numeric\n         / nullif(count(*), 0)                                 AS rate\nFROM visits\nGROUP BY branch;"},{"t":"p","runs":[{"text":"nullif(x, 0)","code":true},{"text":" returns NULL when "},{"text":"x","code":true},{"text":" 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 "},{"text":"::numeric","code":true},{"text":" cast, which the lesson on averages explained; without it two integer counts divide to "},{"text":"0","code":true},{"text":"."}]},{"t":"h2","id":"the-average-of-rates-is-not-the-rate","runs":[{"text":"The average of rates is not the rate"}]},{"t":"p","runs":[{"text":"You have a daily table with a "},{"text":"rate","code":true},{"text":" column. Last month’s rate is not "},{"text":"avg(rate)","code":true},{"text":". 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."}]},{"t":"p","runs":[{"text":"The rule is the same as for averages: keep the numerator and denominator as separate columns, sum each, and divide the sums."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT sum(conversions)::numeric / nullif(sum(visits), 0) AS pooled_rate\nFROM daily_stats\nWHERE day >= '2026-03-01' AND day < '2026-04-01';"},{"t":"h2","id":"both-halves-must-be-drawn-from-the-same-pile","runs":[{"text":"Both halves must be drawn from the same pile"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-trick-with-booleans","runs":[{"text":"The trick with booleans"}]},{"t":"p","runs":[{"text":"When the numerator is “rows where something is true”, the rate is the average of a 0/1 column:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT avg(CASE WHEN converted THEN 1 ELSE 0 END) AS rate FROM visits;\n-- DuckDB and Postgres also accept avg(converted::int)"},{"t":"p","runs":[{"text":"It is compact, it is portable, and it fails safely on an empty group by returning NULL."}]},{"t":"h2","id":"percentage-change-has-its-own-trap","runs":[{"text":"Percentage change has its own trap"}]},{"t":"p","runs":[{"text":"“Up 40% on last month” is "},{"text":"(this - last) / last","code":true},{"text":". When "},{"text":"last","code":true},{"text":" is zero the expression divides by zero; when "},{"text":"last","code":true},{"text":" is negative, as a net figure can be, the sign flips and “up” becomes “down”. Guard with "},{"text":"nullif","code":true},{"text":" and, for figures that can be negative, report the absolute change alongside the percentage or instead of it."}]},{"t":"h2","id":"small-denominators","runs":[{"text":"Small denominators"}]},{"t":"p","runs":[{"text":"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 "},{"text":"n","code":true},{"text":" in the next column, always, and consider hiding rates where "},{"text":"n","code":true},{"text":" 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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A weekly report computes conversion rate as "},{"text":"avg(daily_rate)","code":true},{"text":" from a table with one row per day. Traffic is heavy on weekdays and very light at weekends. Compared with the true weekly rate, what does the report show?"}],"options":[{"text":[{"text":"The same value, since every day contributes equally to a week."}],"correct":false,"tempting":[{"text":"Days contribute equally to the average of rates precisely because their traffic is ignored, which is the error."}]},{"text":[{"text":"A value that is too low, because weekend days with few visits post a rate of zero whenever they have no conversions, and two such days drag the seven-day mean down."}],"correct":false,"tempting":[{"text":"Assumes the direction of the error; a two-visit day can just as easily post 50% from one sale, and the fault is the equal weighting, not the sign."}]},{"text":[{"text":"A value pulled towards the weekend rates, which barely affect the true rate; the fix is to sum conversions and visits separately and divide."}],"correct":true,"tempting":null},{"text":[{"text":"A value that is too high, because "},{"text":"avg()","code":true},{"text":" skips NULL days and the weekend rows are NULL."}],"correct":false,"tempting":[{"text":"Assumes weekend rows are NULL; the scenario says they are light, not missing, and NULL skipping would not explain a rate drawn towards them."}]}],"order":[0,3,2,1],"why":null},"prev":{"slug":"grouping-by-time","title":"Grouping by day, week and month without lying about the boundaries","minutes":10},"next":{"slug":"string-aggregation-and-arrays","title":"Listing the members of a pile: string_agg and array_agg","minutes":7}}}