SQL conditional aggregation: rates with an auditable denominator

Last updated: 5 Oct 20265 min read

tutorial

A rate query should expose its numerator, denominator and missing-outcome count before it divides.

Start from the eligible base

Build the denominator from a frozen intake cohort, not from rows that happen to survive a join to outcomes. A LEFT JOIN preserves eligible cases without review; a WHERE predicate on the joined outcome can silently turn it into an inner join. If the metric is review completion, the denominator is all eligible cases. If the metric is positive among reviewed cases, the denominator is reviewed cases. Label those rates separately. Cohort rules belong in query comments and review notes.

Aggregate states before dividing

Count eligible, observed, positive and unknown with explicit CASE expressions. Protect a zero denominator with NULLIF or an equivalent guard, and do not convert the resulting undefined rate to zero. Zero means a measured rate of zero; null means no rate can be computed under the rule. Save the integer counts so readers can re-evaluate a percentage at another precision.

Avoid multiplication through joins

A case may have three status events and two reviewer notes. Joining both raw tables to the case table yields six combinations for that case. COUNT(DISTINCT case_id) can hide some symptoms while sums and subgroup fields remain wrong. Reduce each child to one row per case under a declared rule before joining. Join cardinality must be tested explicitly.

Keep time windows explicit

A case submitted near the analysis cutoff may not have enough follow-up for a two-day resolution outcome. A CASE expression cannot fix that by itself. Store follow_up_eligible as a separate condition, and report how many cases fall outside it. If late events can revise an outcome, pin the event snapshot used by the query. Cohort entry and observation time are separate columns.

Read a concrete result

In a five-case fixture, three have outcomes, two are positive and two are unknown. The observed positive rate is 2/3; the reviewed fraction is 3/5. The SQL returns both counts and leaves the analyst to choose the rate that matches the decision. A dashboard that shows only 66.7% conceals the 40% unobserved share.

Implementation

WITH case_outcomes(case_id, eligible, review_outcome) AS (
    VALUES (301, 1, 1), (302, 1, 0), (303, 1, NULL),
           (304, 1, 1), (305, 1, NULL)
), counts AS (
    SELECT SUM(CASE WHEN eligible = 1 THEN 1 ELSE 0 END) AS eligible_count,
           SUM(CASE WHEN eligible = 1 AND review_outcome IS NOT NULL THEN 1 ELSE 0 END) AS reviewed_count,
           SUM(CASE WHEN eligible = 1 AND review_outcome = 1 THEN 1 ELSE 0 END) AS positive_count
    FROM case_outcomes
)
SELECT eligible_count, reviewed_count, positive_count,
       1.0 * positive_count / NULLIF(reviewed_count, 0) AS positive_among_reviewed,
       1.0 * reviewed_count / NULLIF(eligible_count, 0) AS reviewed_fraction
FROM counts;

Performance and operating cost

The aggregate is O(N) time and O(1) memory before grouping. In a warehouse, pruning by cohort window and joining already deduplicated outcome rows reduces scan cost; COUNT(DISTINCT) over an exploded join can require much more memory and still leave other measures wrong.

Common Mistakes

Do not filter joined outcomes in WHERE when the cohort must retain unobserved cases.
Do not publish a ratio without its integer numerator and denominator.
Do not use COUNT(DISTINCT) as a substitute for defining one outcome row per case.