Conditional Aggregation: Beginner

A WHERE clause keeps or drops whole rows, so a query with WHERE status = 'active' can produce the active count and nothing else: the inactive rows left the query before GROUP BY ever saw them. The output the interviewer described needs the active count and the inactive count in the same row, which means both sets of rows have to reach the group, and each column has to decide for itself which of the grouped rows it counts. So the condition goes inside the aggregate and is checked against each row.

Read the answer table column by column and the query follows from it. The column that labels each row, region, is the GROUP BY column. Every other column is an aggregate over that region's rows. A column named after a value in the data is an aggregate that counts only the rows carrying that value. active is one; premium and converted work the same way. The way to say 'only these rows' inside an aggregate is a CASE that returns something for a matching row and NULL or 0 for the rest. The total column is the same aggregate with no condition, and North's 0 under inactive is a region with no matching rows still getting its row.

The request also arrives from the other direction. The interviewer pastes a 3-row sample of the dashboard tile they want filled and asks for the query behind it. The same reading applies: find the column whose values are labels, treat it as the group, and treat every numeric column as an aggregate whose name tells you its condition. A tile headed premium_revenue is SUM over amount where tier is premium; one headed orders with no qualifier is COUNT(*).

State the plan in one sentence before typing: 'I'll group by region and use conditional aggregates, a CASE inside each COUNT or SUM, to get one column per status.' That sentence shows the interviewer you recognized the shape and know the technique by name, and it commits you to a single query, so the next 60 seconds are typing rather than deciding. A candidate who opens with SELECT region, COUNT(*) FROM customers WHERE status = 'active' is answering a third of the question and will throw the query away when asked where the other columns are.

CASE runs once per row, before any aggregate sees the row. CASE WHEN tier = 'premium' THEN 1 ELSE 0 END looks at one order and produces a 1 if the tier is premium and a 0 otherwise. SUM then adds those 1s and 0s across the group, and because every 0 adds nothing, the result is the number of premium orders. The row stayed in the group the whole time; the condition decided its contribution. That is the difference between this and a WHERE, and the interviewer will ask you to state it.

The orders table below has 3 rows in North and 1 in South. The query adds a second conditional column that uses the same condition with amount in place of 1, so you can watch each row resolve to 1 or 0 for the count and to its amount or 0 for the revenue before the groups fold.

North holds orders 1 to 3. Order 1 is premium and becomes 1 and 100; order 2 is standard and becomes 0 and 0; order 3 is premium and becomes 1 and 200. The sums are 2 premium orders, 300 in premium revenue, and 350 in total revenue from the unconditional SUM(amount). South holds only order 4, a standard order at 300, so both of its CASE results are 0 and only the total column shows 300: a region with no premium orders still gets its row, with 0 in the premium columns. The standard orders contributed 0 to the premium columns, which is why the total column still sees their 50 and 300.

The count form and the sum form are one pattern with the THEN swapped. THEN 1 counts matching rows; THEN amount sums their amounts; THEN amount * 0.1 would sum a derived value. When the interviewer adds 'and premium revenue' to a question about premium order counts, the new column is the same CASE with a different THEN. Saying so, 'same condition, the value changes from 1 to amount', shows you see one pattern rather than 2 separate tricks.

Dropping the ELSE makes non-matching rows produce NULL, and SUM skips NULL, so the count comes out the same on any group that has a match. The 2 forms differ in one case the interviewer likes to ask about: a region with no premium orders at all. With ELSE 0 that region's column is 0. With no ELSE, every row in the group produced NULL, and a SUM over nothing but NULL returns NULL. A dashboard tile showing a blank where the reader expects 0, or a downstream subtraction that turns into NULL, is what the follow-up is about. Write ELSE 0 inside SUM unless you have a reason to want the NULL, and say that reason.

The condition can use AND and OR to combine comparisons, and IN lists and date ranges work too, because CASE evaluates a boolean and nothing about SUM cares how that boolean was built. The slice gets narrower; the structure stays one column per slice, and a follow-up such as 'now only premium orders from this quarter' is an AND added to a condition rather than a new query.

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.

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.

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 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.

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.

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.

A question that asks for active and inactive customers per region, plus the total, carries an unstated requirement: active plus inactive should equal the total. Columns built to split a total need 2 properties. Every row has to land in some column, so the conditions are exhaustive, and no row can land in 2 columns, so the conditions are exclusive. A CASE per column gives you neither for free, and the interviewer who asks 'do these add up?' is checking whether you know that.

A customer whose status is NULL breaks the first property. The comparison status = 'active' evaluates to NULL rather than false, CASE treats anything other than true as not matching, and with no ELSE the row produces NULL for every status column. COUNT(*) counts the row anyway. The same happens to a value nobody wrote a column for: a churned customer in a query with only active and inactive columns is in the total and in no slice. The run below has 1 of each.

The other_count condition needs both halves. status NOT IN ('active', 'inactive') is NULL when status is NULL, so on its own it would skip the very row it exists to catch; the IS NULL test is what picks that row up. With the catch-all in place, both regions show a total of 3 and a 1 in each slice column, so the slices add back up to the total.

The run has 4 orders, and the 3 columns sum to 5. The fix is to make the ranges disjoint, amount >= 100 AND amount <= 500 for the middle bucket, or to assign each row one label first and count the labels.

A CASE with several WHEN branches evaluates them in order and stops at the first true one, and the ELSE catches whatever remains, including the NULL status. That gives every row exactly one label by construction, so the counts over the labels are exhaustive and exclusive without any further checking. The 2-step shape costs a CTE, and it pays for itself the moment a fourth status value appears in the data: the new value falls into other and the total still reconciles, rather than vanishing from a dashboard.

The question shifts from a customers table to an events table and the wording barely changes: 'how many users made a purchase, per region'. The events table has one row per event, so a user who bought 3 times has 3 purchase rows. COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) counts purchases, and a candidate who reports that number as the number of buyers has answered a different question. The grain of the input decides which aggregate the question needs, and saying the grain out loud is the first thing the interviewer listens for.

Counting the users rather than the rows puts DISTINCT inside the aggregate and the user id in the THEN: COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END).

The CASE returns the user_id for a purchase row and NULL for every other row. DISTINCT collapses the repeated ids into one each, COUNT skips the NULL, and what remains is the set of users with at least 1 purchase in the group. The run below has 6 events across 2 regions, with user 101 buying twice in North and user 202 buying twice in South, so the 2 counts come apart in both rows.

North holds events 1 to 4: user 101 purchases twice, user 102 views, user 103 purchases. The purchase count is 3; the CASE produced 101 twice, then NULL, then 103, and after DISTINCT that leaves 2 buyers. South holds events 5 and 6, both purchases by user 202, which makes 2 purchases from a single buyer. On a real table that gap is a repeat customer, 3 purchases from 2 buyers, and a dashboard that showed 3 under 'buyers' would overstate the customer base by half.

The disguise is in the noun. 'Purchases' and 'orders' and 'events' are rows; 'users', 'customers', 'accounts' and 'sessions' are distinct ids. The interviewer often asks for both in one breath, 'purchases and buyers per region', and wants to see the pair as 2 columns from the same group rather than 1 column with the wrong name. A WHERE event_type = 'purchase' with COUNT(DISTINCT user_id) gets the buyers right but loses the viewers, which is the same shape problem as the opening question: the condition belongs inside each aggregate so that buyers and viewers can sit in one row beside the overall count.

Each DISTINCT is computed inside its own aggregate, independently of the others. A user who viewed and then bought is in both buyers and viewers, so those 2 columns can sum to more than all_users. The previous section's reconciliation check does not apply here, and saying so unprompted, 'these are overlapping sets of users, so they won't add up to all_users', is the sentence that separates someone who has built this from someone who has only read the syntax. If the question needs non-overlapping groups of users, label each user once first, by their highest action, and count the labels.