Scalar aggregate (single COUNT(*)) with a compound WHERE filter on a status enum and a date range. Practice two-condition WHERE clauses, inclusive date-range filters on YYYY-MM-DD strings, and the headline-number scalar query shape.
Denials operations review is tomorrow morning and I need the headline number first. From fact_claims, give me the count of claims that were denied with a service_date in Q4 2025 (October, November, and December 2025, inclusive). One number out — denied_q4. The status column uses lowercase 'denied'. This is the metric the VP opens the meeting with; the breakdowns by reason and provider come later in the deck, but if I get the top-line number wrong everything downstream is suspect.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| member_id | INT | FK → dim_members |
| provider_id | INT | FK → dim_providers_payer |
| plan_id | INT | FK → dim_plans |
| service_date | TEXT | |
| cpt_code | TEXT | |
| place_of_service | TEXT | |
| billed_amt | REAL | |
| allowed_amt | REAL | |
| paid_amt | REAL | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Scalar aggregate: a single COUNT(*) with no GROUP BY. The output is one row, one column — that's the headline-number shape.
Two filter conditions joined with AND: a status equality on the lowercase enum value, and a service_date range that covers October through December 2025 inclusive on both ends.
service_date is a YYYY-MM-DD string; lexicographic comparisons (>=, <=) are date-correct on that format. 'Inclusive on both ends' is the load-bearing word in the brief — pick comparison operators that don't quietly drop the last day of December.