CTE for Patients with Multiple Diagnoses | Dataford Interview Questions

Your question is CTE for Patients with Multiple Diagnoses. Start with the requirements and the two 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.

Problem

Novo Nordisk clinical analytics needs to identify patients who received multiple distinct diagnoses during the same calendar month. Write a PostgreSQL query using a common table expression (CTE).

Requirements

Exclude diagnosis records with a missing diagnosis date or diagnosis code.
Group diagnoses by patient and calendar month, counting distinct diagnosis codes.
Return only patient-month groups with more than one distinct diagnosis.
Join patient details and include the diagnosis codes in alphabetical order.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient name

diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis record identifier
patient_id	INT	Referenced patient identifier
diagnosed_at	DATE	Date on which the diagnosis was recorded
diagnosis_code	VARCHAR(50)	Diagnosis code or clinical diagnosis description