Conditional counts and rates

Most analytics questions end in a rate: a share of users, a failure rate, a conversion. When you present one, the interviewer checks two things before anything else: what you divided by, and whether 1 / 6 came back as 0. This lesson shows how to read a query in the order the database runs it, count several conditions in one pass, compute a rate that survives integer division and empty groups, tell an overall rate from an average of group rates, and test whether every row in a group meets a condition.

The order a query runs in

You write SELECT first, but the database works through the clauses in a different order. This is the logical order: the planner may execute steps differently, but the result always behaves as if it followed this sequence.

One rule follows from it: WHERE runs before any group exists, so it cannot test an aggregate, while HAVING runs after grouping, so it can.

Column aliases follow their own rules. In PostgreSQL, a SELECT alias cannot be used in WHERE or HAVING. ORDER BY accepts it on its own but not inside an expression, and GROUP BY accepts it as a PostgreSQL extension. In a live round, repeat the expression or name it in a CTE.

The examples in this lesson come from a team's continuous integration (CI) system, which starts a build on every push. A build ends as passed, failed or cancelled. builds has one row per build:

Which repos had at least two failed builds?

SELECT repo, COUNT(*) AS failed_builds
FROM builds
WHERE status = 'failed'
GROUP BY repo
HAVING COUNT(*) >= 2
ORDER BY failed_builds DESC;

Follow the steps. WHERE keeps the 3 failed builds, so docs, which has none, never forms a group. GROUP BY makes one group for api with 1 row and one for web with 2. HAVING drops api. SELECT names the count failed_builds, and ORDER BY can sort by that name.

Write HAVING failed_builds >= 2 and PostgreSQL stops with column "failed_builds" does not exist. Write WHERE COUNT(*) >= 2 and the error is aggregate functions are not allowed in WHERE. Both errors follow from the rules above.

Count several conditions in one pass

Conditional aggregation puts a condition inside an aggregate, so one GROUP BY can count several things side by side. The portable form wraps a CASE expression in SUM:

SELECT repo,
       SUM(CASE WHEN status = 'passed'    THEN 1 ELSE 0 END) AS passed,
       SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END) AS failed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       COUNT(*) AS builds
FROM builds
GROUP BY repo
ORDER BY repo;

Keep the ELSE 0. Without it, the CASE returns NULL for every non-matching row, and docs, which has no failed build, gets NULL in the failed column instead of 0, because SUM over nothing but NULLs is NULL. Keep it with SUM only. COUNT(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) counts every row, because 0 is not NULL; with COUNT, write THEN 1 END and no ELSE.

PostgreSQL also has the FILTER clause, which says the same thing more directly:

SELECT repo,
       COUNT(*) FILTER (WHERE status = 'passed')    AS passed,
       COUNT(*) FILTER (WHERE status = 'failed')    AS failed,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       COUNT(*) AS builds
FROM builds
GROUP BY repo
ORDER BY repo;

It returns the same table, and a COUNT with FILTER gives 0, never NULL, for an empty condition. FILTER is standard SQL, but many engines do not support it, among them MySQL, SQL Server and BigQuery; the CASE form runs everywhere, so use it when you are not sure of the dialect.

Both queries are also a pivot: each known value of status became its own column. That works when you can list the values in advance. A new status added next month needs a new line, which is a fair thing to mention when the interviewer asks how your query would age.

A rate is a numerator over a denominator you chose

A failure rate looks like one number, but it is two counts and a decision. The first attempt divides failed builds by all builds:

SELECT repo,
       COUNT(*) FILTER (WHERE status = 'failed') / COUNT(*) AS failure_rate
FROM builds
GROUP BY repo
ORDER BY repo;

Every repo shows 0, including web, which failed 2 of its 3 builds. Both counts are integers, and in PostgreSQL an integer divided by an integer is an integer with the fraction thrown away: 5 / 2 is 2, and 2 / 3 is 0. MySQL and BigQuery return a fraction for 5 / 2, so a query that worked there can return zeros here. Multiply by 100.0 (or 1.0 for a fraction) before dividing, so the arithmetic happens in decimals.

The denominator is the bigger decision. A cancelled build neither passed nor failed: someone stopped it. Counting it in the denominator makes api's rate 16.7% (1 of 6 builds); counting only finished builds makes it 20% (1 of 5). Both are defensible, so say which one you mean.

"Numerator: failed builds. Denominator: finished builds, so passed plus failed, because a cancelled build never got a verdict. If you'd rather treat cancelled as failed, that's a one-line change."

With finished builds as the denominator, docs has none, and 100.0 * failed / finished stops the query with division by zero. NULLIF(x, 0) returns NULL when x is 0, and dividing by NULL gives NULL, so the empty repo gets a NULL rate instead of an error. Count first, then divide, and round once at the end:

WITH counts AS (
    SELECT repo,
           COUNT(*) FILTER (WHERE status = 'failed')              AS failed,
           COUNT(*) FILTER (WHERE status IN ('passed', 'failed')) AS finished
    FROM builds
    GROUP BY repo
)
SELECT repo, failed, finished,
       ROUND(100.0 * failed / NULLIF(finished, 0), 1) AS failure_pct
FROM counts
ORDER BY repo;

NULL is the honest answer for docs: it has no finished builds, so it has no failure rate. A 0% there would claim a clean record it does not have. Keeping the counts as columns also lets the reader see that web's 66.7% rests on 3 builds.

Use 100.0 rather than a cast to float. In PostgreSQL, ROUND with a number of decimal places takes a numeric, so ROUND(failed::float / NULLIF(finished, 0), 1) fails with function round(double precision, integer) does not exist.

One more denominator trap: GROUP BY only makes groups from rows that exist. A repo with no builds at all is not in builds, so it cannot show up with a NULL or a 0. To list every repo, start from a table of repos and LEFT JOIN the counts to it, as in joins without surprises.

An overall rate is not the average of group rates

With a rate per repo in hand, it is tempting to average those rates for a company-wide number. Compare the two:

WITH counts AS (
    SELECT repo,
           COUNT(*) FILTER (WHERE status = 'failed')              AS failed,
           COUNT(*) FILTER (WHERE status IN ('passed', 'failed')) AS finished
    FROM builds
    GROUP BY repo
)
SELECT ROUND(100.0 * AVG(failed::numeric / NULLIF(finished, 0)), 1) AS avg_of_repo_rates,
       ROUND(100.0 * SUM(failed) / NULLIF(SUM(finished), 0), 1)    AS overall_rate
FROM counts;

They answer different questions. 37.5% is the share of finished builds that failed: 3 of 8. 43.3% is the mean of the repo rates, with every repo weighted equally, so web's 3 builds weigh as much as api's 5. If the question is "what share of builds fail", it wants the overall rate: sum the numerators, sum the denominators, divide once.

What the interviewer sees when you average the group rates is 43.3% where the question asked for 37.5%, and the follow-up is easy to predict: split api into two repos without changing a single build, and your number moves while the share of failed builds stays put. AVG also skipped docs, whose rate is NULL; had you turned that NULL into 0 first, the average would drop to 28.9%. Rounding the repo rates to whole percents before averaging moves it again, to 43.5, which is why rounding waits until the last step.

When every row in a group must meet a condition

Some conditions are about all the rows in a group. In the same CI system, a pull request can merge only when every check on it passed. checks has one row per check per pull request, and status stays NULL until the check reports:

PostgreSQL's BOOL_AND(condition) is true when the condition is true on every row where it is not NULL (and NULL when it is NULL on all of them). The other common form turns each row into a 1 or a 0 and takes the MIN, which is 1 only if every row is 1:

SELECT pr_id,
       BOOL_AND(status = 'passed')                            AS bool_and_result,
       MIN(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) = 1 AS all_passed
FROM checks
GROUP BY pr_id
ORDER BY pr_id;

They disagree on PR 43. Like SUM and MIN, BOOL_AND skips NULL inputs, and for the unreported build check status = 'passed' is NULL, so BOOL_AND judges PR 43 on its two passed checks alone and calls it ready. The MIN form sends NULL to the ELSE branch, so an unreported check counts as not passed. If you prefer BOOL_AND, write BOOL_AND(COALESCE(status = 'passed', false)), which also returns false for PR 43. The same pattern with MAX, or BOOL_OR, answers "at least one row".

To keep only the qualifying groups, move the condition to HAVING: HAVING MIN(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) = 1 returns PR 41 alone. Both forms judge only the rows present. If a check may not have a row yet, also require the full set, for example COUNT(*) FILTER (WHERE status = 'passed') = 3, or join to a table of required checks.

Practicing with the questions on this page

Open each question and, before writing, say the numerator and the denominator in words, including which rows each one leaves out, and whether the question wants one overall rate or a rate per group. Then:

1. Count the numerator and the denominator as separate columns, and look at them before you divide.

2. Divide with 100.0 * in front, wrap the denominator in NULLIF, and round once at the end.

3. For "every" and "all" conditions, decide what a NULL row means before you pick BOOL_AND or MIN.

Run it in the console and sanity-check one group by hand: a rate built from 2 and 3 should read 66.7, not 0 or 67. Deciding whether a difference between two rates is real, rather than noise, is a statistics question; the A/B testing course covers it.