Top Diagnoses by Month | Dataford Interview Questions

Caris Life Sciences uses molecular and clinical diagnosis data to monitor trends across its patient population. Write a PostgreSQL query that identifies the three most common diagnoses by distinct patient count for each month.

Requirements

Join diagnosis_events to patient_registry and include only events for registered patients.
Group records by calendar month and diagnosis, counting each patient at most once per month and diagnosis.
Rank diagnoses within each month by patient count descending, using diagnosis name ascending as the deterministic tie-breaker.
Return the top three diagnoses available in each month, ordered by month and rank.

Schema

patient_registry

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_code	VARCHAR(20)	Caris patient reference code
sex	VARCHAR(20)	Recorded patient sex

diagnosis_events

Column	Type	Description
event_idPK	INT	Unique diagnosis event identifier
patient_id	INT	References patient_registry.patient_id
diagnosis_name	VARCHAR(100)	Diagnosis or biomarker-associated condition
diagnosed_at	DATE	Date of diagnosis event

Your question is Top Diagnoses by Month. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem