Top Diagnoses Per Hospital | Dataford Interview Questions

Write a SQL query to find the top 3 most frequent diagnoses per hospital.

Use the provided hospital and patient diagnosis data. Exclude records with a NULL diagnosis. If frequencies tie, order diagnoses alphabetically to produce a deterministic top-three result.

Output

One row per selected hospital and diagnosis, with columns hospital_name, diagnosis, diagnosis_count, and diagnosis_rank.
Include only the top three diagnoses for each hospital, ordered by hospital name and rank.

Schema

hospitals

Column	Type	Description
hospital_idPK	INT	Unique hospital identifier
hospital_name	VARCHAR(100)	Hospital name
region	VARCHAR(50)	Hospital service region

patient_diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis record identifier
hospital_id	INT	Hospital associated with the diagnosis record
diagnosis	VARCHAR(100)	Recorded patient diagnosis
diagnosed_on	DATE	Date the diagnosis was recorded

Your question is Top Diagnoses Per Hospital. 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