Open up dim_members and dim_plans, the membership backbone of every payer query. Practice a two-table INNER JOIN, a NULL-aware filter on enrollment_end, GROUP BY on a plan attribute, and ORDER BY DESC — the basic shape of the active-membership snapshot every payer ops team rebuilds weekly.
Welcome to the analytics bench. Steering committee meets Thursday and the first slide is always the active membership cut. From dim_members joined to dim_plans, give me a count of currently-active members at each plan_type — plan_type and active_members, sorted with the largest book at the top. "Active" means enrollment_end IS NULL or enrollment_end >= '2025-12-31' (treat 2025-12-31 as today). Four plan types in the book: HMO, PPO, EPO, POS. This is the headline number every downstream payer mission references — getting dim_members and dim_plans in your fingers now pays off all month.
| Column | Type | Key |
|---|---|---|
| member_id | INT | PK |
| plan_id | INT | FK → dim_plans |
| age_band | TEXT | |
| sex | TEXT | |
| region | TEXT | |
| enrollment_start | TEXT | |
| enrollment_end | TEXT |
| Column | Type | Key |
|---|---|---|
| plan_id | INT | PK |
| plan_name | TEXT | |
| plan_type | TEXT | |
| funding | TEXT | |
| metal_tier | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table aggregate: members joined to plans on the plan key, then grouped on a plan attribute. No claims table needed — membership lives in the dimension layer.
Active membership is a NULL-aware filter on the enrollment_end date. NULL means "still enrolled, no termination on file"; an explicit far-future date means the same thing for our purposes. Either OR the two conditions, or use IS NULL together with a date comparison.
When the rank ladder is short (just four plan types) the eye still benefits from largest-first; that's why the descending sort matters more than it would on a five-row report — small N makes the order itself the story.