Pivot Diagnosis Counts by Category

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Pivot Diagnosis Counts by Category. Start with the requirements and the four 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.

You need to log in / sign up to run or submit.

Problem

Novo Nordisk needs a monthly view of diagnosed patients by clinical category for analytics reporting. Write a PostgreSQL query that pivots selected diagnosis categories into separate columns.

Requirements

Include January through March 2025, including months with no qualifying diagnoses.
Join patients, encounters, diagnosis events, and diagnosis categories.
Include only completed encounters for patients who consented to analytics and whose country is Denmark.
Count each patient at most once per month and diagnosis category, even if multiple diagnosis events exist.
Return columns for Type 2 Diabetes, Obesity, Cardiovascular, and Rare Disease, plus the total number of distinct diagnosed patients per month.
Order the output chronologically by month.

Schema

patients

Column	Type	Description
patient_idPK	INT	Patient identifier
patient_name	VARCHAR(100)	Patient name
country	VARCHAR(80)	Patient country
analytics_consent	BOOLEAN	Whether analytics use is permitted

encounters

Column	Type	Description
encounter_idPK	INT	Encounter identifier
patient_id	INT	Referenced patient
encounter_date	DATE	Date of encounter
status	VARCHAR(30)	Encounter status

diagnosis_events

Column	Type	Description
diagnosis_event_idPK	INT	Diagnosis event identifier
encounter_id	INT	Referenced encounter
patient_id	INT	Referenced patient
category_id	INT	Referenced diagnosis category
diagnosis_code	VARCHAR(20)	Clinical diagnosis code

diagnosis_categories

Column	Type	Description
category_idPK	INT	Category identifier
category_name	VARCHAR(100)	Clinical diagnosis category
therapy_area	VARCHAR(100)	Broader therapy area

Tablespatientsencountersdiagnosis_eventsdiagnosis_categories

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

Pivot Diagnosis Counts by Category

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Pivot Diagnosis Counts by Category. Start with the requirements and the four 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.

You need to log in / sign up to run or submit.

Problem

Novo Nordisk needs a monthly view of diagnosed patients by clinical category for analytics reporting. Write a PostgreSQL query that pivots selected diagnosis categories into separate columns.

Requirements

Include January through March 2025, including months with no qualifying diagnoses.
Join patients, encounters, diagnosis events, and diagnosis categories.
Include only completed encounters for patients who consented to analytics and whose country is Denmark.
Count each patient at most once per month and diagnosis category, even if multiple diagnosis events exist.
Return columns for Type 2 Diabetes, Obesity, Cardiovascular, and Rare Disease, plus the total number of distinct diagnosed patients per month.
Order the output chronologically by month.

Schema

patients

Column	Type	Description
patient_idPK	INT	Patient identifier
patient_name	VARCHAR(100)	Patient name
country	VARCHAR(80)	Patient country
analytics_consent	BOOLEAN	Whether analytics use is permitted

encounters

Column	Type	Description
encounter_idPK	INT	Encounter identifier
patient_id	INT	Referenced patient
encounter_date	DATE	Date of encounter
status	VARCHAR(30)	Encounter status

diagnosis_events

Column	Type	Description
diagnosis_event_idPK	INT	Diagnosis event identifier
encounter_id	INT	Referenced encounter
patient_id	INT	Referenced patient
category_id	INT	Referenced diagnosis category
diagnosis_code	VARCHAR(20)	Clinical diagnosis code

diagnosis_categories

Column	Type	Description
category_idPK	INT	Category identifier
category_name	VARCHAR(100)	Clinical diagnosis category
therapy_area	VARCHAR(100)	Broader therapy area

Tablespatientsencountersdiagnosis_eventsdiagnosis_categories

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results