Your question is Join Patient Records and Aggregate. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Novo Nordisk's clinical analytics team needs a patient-level summary of diagnostic activity from its clinical data platform. Write a PostgreSQL query that joins patient records with diagnostic codes and aggregates confirmed diagnoses recorded during 2025.
NULL when no code matches.Apply the date and status conditions without removing patients who have no qualifying records.
| Column | Type | Description |
|---|---|---|
| patient_idPK | INT | Unique patient identifier |
| patient_name | VARCHAR(100) | Patient display name |
| Column | Type | Description |
|---|---|---|
| diagnosis_idPK | INT | Unique diagnosis record identifier |
| patient_id | INT | References patients.patient_id |
| diagnostic_code_id | INT | References diagnostic_codes.diagnostic_code_id |
| diagnosed_on | DATE | Date on which the diagnosis was recorded |
| status | VARCHAR(20) | Diagnosis workflow status |
| Column | Type | Description |
|---|---|---|
| diagnostic_code_idPK | INT | Unique diagnostic code identifier |
| code | VARCHAR(20) | Standardized diagnostic code |
| description | VARCHAR(200) | Human-readable code description |