Top 10 Frequent Diagnoses SQL | Dataford Interview Questions

Write a SQL query to retrieve the top 10 most frequent diagnoses from a patient database at Massachusetts General Hospital.

Use the diagnosis_records table and the diagnosis_codes lookup table. Count recorded diagnosis occurrences, exclude records without a matching diagnosis code, and return fewer than 10 rows if fewer than 10 valid diagnoses exist.

Output

One row per diagnosis with columns diagnosis_code, diagnosis_name, and diagnosis_count.
Sort by diagnosis_count descending, then diagnosis_code ascending to break ties.

Schema

diagnosis_records

Column	Type	Description
record_idPK	INT	Unique diagnosis record identifier
patient_id	INT	De-identified patient identifier
diagnosis_code	VARCHAR(10)	Recorded diagnosis code
diagnosed_on	DATE	Date the diagnosis was recorded

diagnosis_codes

Column	Type	Description
diagnosis_codePK	VARCHAR(10)	Standard diagnosis code
diagnosis_name	VARCHAR(150)	Human-readable diagnosis name

Your question is Top 10 Frequent Diagnoses SQL. 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