Second-Highest by Facility | Dataford Interview Questions

Write a query to find the second-highest patient transaction amount within each healthcare facility using window functions for Health Care Solutions At Home.

Return only facilities with at least two distinct transaction amounts. Include ties for the second-highest amount.

Output

One row per qualifying transaction, with facility_id, facility_name, transaction_id, patient_id, and transaction_amount.
Exclude transactions without a matching facility and facilities without a second-highest amount.
Order by facility_id, transaction_amount descending, and transaction_id.

Schema

healthcare_facilities

Column	Type	Description
facility_idPK	INT	Unique healthcare facility identifier
facility_name	VARCHAR(150)	Healthcare facility name
facility_type	VARCHAR(60)	Type of healthcare facility

patient_transactions

Column	Type	Description
transaction_idPK	INT	Unique patient transaction identifier
facility_id	INT	Referenced healthcare facility identifier
patient_id	VARCHAR(30)	Patient identifier
transaction_amount	DECIMAL(10,2)	Amount recorded for the patient transaction

Your question is Second-Highest by Facility. 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