Hiring funnel by role

Constraints

1. 1An undecided application has not ended in an offer, so it belongs in not_offered. Watch what outcome <> 'offer' does to a NULL — the comparison is not true, and it is not false either.
2. 2applications must count every row for the role, decided or not.
3. 3offer_spend must be a number for every role. A role that made no offers should report 0, not an empty cell.
4. 4Do not filter rows out in a WHERE clause — every role stays in the result, and every application counts towards its role's total.

Worked example

Take Data Engineer. It has 6 applications: 2 offers, 2 rejected, and 2 nobody has decided yet. not_offered is 4 — the 2 rejections plus the 2 undecided. Write WHERE outcome <> 'offer' and you get 2 instead, because comparing NULL to 'offer' yields NULL rather than true, and only true rows survive a filter. Use this as your own check: for every role, not_offered + offers must equal applications. If a row does not add up, the undecided rows fell out.

What this tests

Three-valued logic: that a comparison against NULL is unknown rather than false, that COUNT(column) skips NULLs while COUNT(*) does not, and that SUM over a group with no values returns NULL instead of 0.