BeginnerHealthcare10 min

Normalize patient sex codes

Challenge

Map messy sex values onto a single-letter code.

Business context

Registration captured F, f, Female, M, and m. Quality reporting needs one code per patient before the file goes to the registry.

Instructions

Return id, patient_name, and sex_code. patient_name is the name with surrounding spaces removed. sex_code is F when the trimmed, uppercased value is F or FEMALE, and M when it is M or MALE. Sort by id.

Schema

patients

id INTEGER · primary key
name TEXT · may include extra spaces
birth_date TEXT
sex TEXT · F, M, Female, or mixed case
city TEXT

encounters

id INTEGER · primary key
patient_id INTEGER
department TEXT
started_on TEXT
ended_on TEXT · nullable while the visit is open
visit_type TEXT · outpatient or inpatient

diagnoses

id INTEGER · primary key
encounter_id INTEGER
code TEXT
description TEXT

claims

id INTEGER · primary key
encounter_id INTEGER
billed_cents INTEGER
paid_cents INTEGER · nullable until adjudicated
status TEXT · paid, denied, submitted
submitted_on TEXT

Sample rows

patients
id	name	birth_date	sex	city
1	Amina Yusuf	1984-04-12	F	Lagos
2	Hugo Berger	1972-11-03	M	Berlin
3	Mei Lin	1990-06-21	f	Shanghai

encounters
id	patient_id	department	started_on	ended_on	visit_type
1	1	Cardiology	2025-01-06	2025-01-06	outpatient
2	1	Cardiology	2025-03-02	2025-03-04	inpatient
3	2	Orthopedics	2025-01-11	2025-01-11	outpatient

diagnoses
id	encounter_id	code	description
1	1	I10	Essential hypertension
2	2	I21.9	Acute myocardial infarction
3	3	M17.1	Knee osteoarthritis

claims
id	encounter_id	billed_cents	paid_cents	status	submitted_on
1	1	18000	15000	paid	2025-01-07
2	2	240000	198000	paid	2025-03-06
3	3	22000	0	denied	2025-01-12

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Catalog sorted by category