Single-table aggregation with conditional count and rate calc. Practice SUM(CASE) for the overturn count, NULLIF on the denominator to defend against divide-by-zero, and the IS NOT NULL filter that excludes in-flight appeals — the canonical staffing-model query for an appeals team.
Building the FY headcount ask. From fact_appeals, give me appeal volume per level and the overturn rate for each — level, appeal_count, overturned, overturn_pct (100.0 * overturned / appeal_count, rounded to 1 decimal). Restrict to appeals with a non-NULL decision_date — pending appeals shouldn't count toward an overturn rate, the decision hasn't happened yet. Three levels (1, 2, 3); sort ascending on level so the table reads top-down through the escalation ladder. Wrap the denominator in NULLIF as a habit — divide-by-zero on a level with no decided appeals would crash the query, and we want the SQL to be safe to run on next quarter's data when level 3 might be empty.
| Column | Type | Key |
|---|---|---|
| appeal_id | INT | PK |
| claim_id | INT | FK → fact_claims |
| level | TEXT | |
| filed_date | TEXT | |
| decision_date | TEXT | |
| outcome | TEXT | |
| appellant_type | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate on fact_appeals. Filter out NULL decision_date in the WHERE clause — those are in-flight appeals that haven't been decided yet.
Two metrics per group, computed in the same GROUP BY: a plain row count for the appeal volume, and a conditional aggregation for the overturned count (a CASE-sum that counts only the overturned rows). Both come from the same scan.
The overturn rate is the conditional count over the row count, float-promoted with 100.0 and rounded to 1 decimal. NULLIF takes two arguments — the expression you're guarding and the value that would make it dangerous. Pick the denominator the brief tells you to protect, and pick the value that would crash the divide.