With COUNT(*) FILTER (WHERE status = 'refunded'), what does a customer group with no refunds return?

## What decides which rows the result has A grouped query builds its result in stages: `FROM`/`JOIN` produces rows, `WHERE` discards some, `GROUP BY` partitions what is left into groups, aggregates are computed per group, `HAVING` discards whole groups, and the SELECT list is projected. The filter clause acts inside step four only — while one aggregate consumes its group's rows. It has no say in which groups exist. So for ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(*) FILTER (WHERE status = 'refunded') AS orders_refunded FROM orders GROUP BY customer_id; ``` a customer with four paid orders and no refunds yields `(4, 0)`. The customer is present because four of their rows reached `GROUP BY`; the refund count is zero because none of those four rows satisfied that aggregate's predicate. ## Zero versus NULL The value returned for a measure with no matching rows depends on the aggregate, not on the filter clause: - `COUNT(*)` and `COUNT(expr)` over an empty input return `0`. - `SUM`, `AVG`, `MIN`, `MAX` over an empty input return `NULL`. So in ```sql SELECT customer_id, COUNT(*) FILTER (WHERE status = 'refunded') AS refund_count, SUM(amount) FILTER (WHERE status = 'refunded') AS refund_value FROM orders GROUP BY customer_id; ``` a refund-free customer gets `(0, NULL)` — a mix that surprises report consumers and breaks naive arithmetic downstream, since `refund_value * 2` and `refund_value + 0` are both NULL. Fix it at the source when the contract wants a number: ```sql COALESCE(SUM(amount) FILTER (WHERE status = 'refunded'), 0) AS refund_value ``` ## Contrast with pushing the predicate into WHERE ```sql SELECT customer_id, COUNT(*) AS orders_refunded FROM orders WHERE status = 'refunded' GROUP BY customer_id; ``` This is a different report. Customers with no refunds contribute no surviving rows, form no group, and are absent from the output — there is no `0` row for them, because SQL cannot invent a group for a key it never saw. That is the right query when you want "customers who have refunds", and the wrong query when you want "refunds per customer, including none". Interviewers like this pair because a dashboard built on the second query quietly shows fewer customers each time refunds go to zero. ## When you do want the group dropped Use `HAVING`, which runs after aggregation and can test the filtered measure itself: ```sql SELECT customer_id, COUNT(*) AS orders_total, COUNT(*) FILTER (WHERE status = 'refunded') AS orders_refunded FROM orders GROUP BY customer_id HAVING COUNT(*) FILTER (WHERE status = 'refunded') > 0; ``` Now you keep the honest `orders_total` for the customers you show, and drop only those with no refunds. The `WHERE` version cannot do that: it would have thrown away the paid rows that `orders_total` needs. ## Groups that do not exist at all One limit worth stating plainly: none of these forms can produce a row for a customer who has no orders whatsoever. That customer has no rows in `orders`, so no group exists for them under any filtering scheme. Getting them into the report requires starting from the customer table and outer-joining the orders — a different query shape, and a different problem from the one the filter clause solves. ## Empty table, no GROUP BY One more corner: an aggregate query with no `GROUP BY` always returns exactly one row, even over an empty table. So `SELECT COUNT(*) FILTER (WHERE status = 'refunded'), SUM(amount) FILTER (WHERE status = 'refunded') FROM orders` on an empty table returns one row of `(0, NULL)` — the same zero-versus-NULL split, in the single-group case. ## Interview framing The question is usually posed as a bug report: "the report used to list every customer, now some are missing" or "why is this column NULL instead of 0 for some rows". A complete answer names the stage each filter acts at, states the 0-versus-NULL rule by aggregate, and gives both fixes — `COALESCE` for the NULL, `HAVING` when you actually meant to drop the group.