Provider Analytics Path · Mission 15 of 25Medium

Patient age at admission

Use date arithmetic with mixed date formats and CASE WHEN for age bucketing

The Brief

Dr. Linda NguyenChief Medical Officerclinical-analytics

I need a breakdown of encounter volume by patient age group at time of admission: 0-17, 18-39, 40-64, 65+. Calculate age from date of birth and admit date — don't use a static age field. Be careful — I've heard some DOB values are stored in a different date format than others.

You'll practice

Date arithmeticCASE bucketsFormat handling

Tables & columns available

fact_encountersfact10 columns

Column	Type	Key
encounter_id	INT	PK
patient_id	INT	FK → dim_patients
provider_id	INT	FK → dim_providers
department_id	INT	FK → dim_departments
admit_date	TEXT
discharge_date	TEXT
diagnosis_code	TEXT
diagnosis_description	TEXT
encounter_type	TEXT
discharge_disposition	TEXT

dim_patientsdim9 columns

Column	Type	Key
patient_id	INT	PK
first_name	TEXT
last_name	TEXT
date_of_birth	TEXT
gender	TEXT
insurance_type	TEXT
zip_code	TEXT
primary_language	TEXT
is_deceased	INT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

Compute age AT the time of admission — not today's age. Watch out: some DOBs are in a different format than the rest

Hint 2

Difference two dates to get years, bucket the result with a conditional expression, then GROUP BY the bucket

Hint 3

`CASE WHEN age < 18 THEN '0-17' WHEN age < ___ THEN '18-39' WHEN age < ___ THEN '40-64' ELSE ___ END AS age_group` — GROUP BY age_group, COUNT(*) each

← Previous

Mission 14

Mission 16