Counting Subsets in One Query

Concepts covered: Conditional Aggregation, Counting, Conditional Logic, Grouping

COUNT has its own version of the same move, and it depends on a rule to say aloud: COUNT(*) counts every row in the group, while COUNT(expression) counts the rows where the expression is not NULL. So COUNT(CASE WHEN status = 'active' THEN 1 END) counts the rows where the CASE produced a 1, and because there is no ELSE, every non-active row produced NULL and was skipped. The 1 carries no meaning beyond being not NULL; THEN 'x' or THEN customer_id would count the same rows.

SELECT
  region,
  COUNT(*)                                          AS total_customers,
  COUNT(CASE WHEN status = 'active'   THEN 1 END)   AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END)   AS inactive_count,
  COUNT(CASE WHEN status = 'churned'  THEN 1 END)   AS churned_count
FROM customers
GROUP BY region;

The run below keeps only the total and active-count columns and runs them over the 6 customers from the first section, so each CASE result and each skipped NULL is visible.

SELECT
  region,
  COUNT(*) AS total_customers,
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count
FROM customers
GROUP BY region

North has customers 1 to 3, and only customer 2 has churned: COUNT(*) is 3, and the CASE produced 1, NULL, 1, so active_count is 2. In South only customer 5 is active, so the row has 3 in total and 1 active; customer 4 is inactive and customer 6 has churned. The full query from the code block fills in the rest of the row. North has no inactive customers and 1 churned, South has 1 of each, and each of those columns ran over the same 3 rows the total did.

The total and its subsets in one row

The reason to keep COUNT(*) beside the conditional counts is that the question nearly always wants a rate, and a rate is a subset over a total. Signups and converters per cohort month is the standard form: COUNT(*) is the denominator, the conditional count is the numerator, and the rate is their ratio computed in the same row. Both numbers came from the same grouped rows, so they cannot disagree about which cohort a signup belongs to or which day the data was pulled.

SELECT
  cohort_month,
  COUNT(*) AS signups,
  COUNT(CASE WHEN completed_onboarding = 1 THEN 1 END) AS converters,
  COUNT(CASE WHEN completed_onboarding = 1 THEN 1 END) * 1.0 / COUNT(*) AS conversion_rate
FROM signups
GROUP BY cohort_month

The 2024-01 cohort has 4 signups, of which 3 completed onboarding, a rate of 0.75. The 2024-02 cohort converted 1 of its 2 signups, for 0.5. The numerator expression is written twice, once as its own column and once inside the ratio, and that repetition is deliberate: the interviewer wants to see the absolute counts next to the rate, because a rate of 1.0 on 1 signup and a rate of 1.0 on 10,000 signups mean different things to the person reading the dashboard.

The * 1.0 in the ratio turns the integer numerator into a decimal before the division, because Postgres truncates an integer divided by an integer, as do SQL Server and SQLite, and 3 / 4 comes back as 0; MySQL's / already returns a decimal, so there the * 1.0 is harmless. The denominator is COUNT(*) of a group that exists, and a group exists only because at least 1 row fell into it, so this particular division can't hit zero.

2 queries plus application code

• One query for signups per cohort, another for converters per cohort
• The ratio computed in Python after matching the 2 result sets by cohort
• A cohort present in one result and absent from the other has to be handled by hand
• The 2 queries can drift apart the next time someone edits one of them

One grouped query

• The rate sits in the same row as the signups and converters it came from
• Every cohort appears exactly once, with converters at 0 where nobody converted
• The ratio is computed where its inputs are
• The consumer can check the rate against the counts beside it

SUM(CASE WHEN cond THEN 1 ELSE 0 END) and COUNT(CASE WHEN cond THEN 1 END) return the same count; pick one and use it for every column in the query, because mixing them reads as not knowing they are equivalent.

Reading it back to the interviewer

Once the query is on the screen, read one output row back: 'for January, 4 people signed up, 3 of them finished onboarding, so the rate is 0.75; the 3 and the 4 came from the same group, which is why I put them side by side.' Reading a row back catches your own mistakes, a wrong column name or a swapped condition, before the interviewer does, and it is the moment the question moves from 'can you write it' to 'do you understand what it returns'.

About this lesson

This section is part of the Conditional Aggregation: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.