Advanced CASE
Real business rules combine several fields and sometimes depend on missing values. Make the priority of the rules visible.
Rule precedence
CASE uses the first true condition. Put exceptional states such as cancelled orders before broad value thresholds if those states should override all other labels.
Conditional aggregation
SUM(CASE WHEN status = 'paid' THEN sales ELSE 0 END) adds only paid sales while preserving the other rows for separate metrics. COUNT(CASE WHEN ... THEN 1 END) counts matches using NULL for non-matches.
NULL-aware rules
A test on a missing value can be unknown. Use IS NULL explicitly when missing information requires a separate category. Do not silently call unknown values “healthy” or “low risk”.
Validate categories
Check boundaries, overlapping conditions, unexpected statuses and the fallback category. Compare category counts with the original row count to confirm that every row is accounted for.