CASE and conditional aggregation

CASE is SQL's if/else. It walks the WHEN branches in order and returns the first match, falling back to ELSE.

Putting CASE inside an aggregate is the pivot trick: each expression counts or sums only the rows matching its condition, so you get one column per category in a single scan.

The same pattern gives you rates: divide a conditional count by the total count.