SQL Window Ranking by Hospital | Dataford Interview Questions

Write a SQL query using window functions to rank patients by risk score within each hospital and return the top 3 per hospital.

Use the patients and hospitals tables. Include only patients with recorded risk scores. Ties are broken by the lower patient_id.

Output

One row per selected patient.
Columns: hospital_id, hospital_name, patient_id, patient_name, risk_score, and risk_rank.
Return no more than three patients per hospital, ordered by hospital_id and risk_rank ascending.

hospitals

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

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
hospital_id	INT	Hospital responsible for the patient
patient_name	VARCHAR(100)	Patient name
risk_score	DECIMAL(5,2)	Calculated patient risk score

Your question is SQL Window Ranking by 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.