Top Prescribers SQL by State | Dataford Interview Questions

Write a SQL query to find the top three prescribers for a specific medication in each state, partitioned by year.

Use the provided prescription, prescriber, and medication data. For this exercise, measure prescriber activity by prescription count for Ozempic. Exclude records without a known state and return no more than three prescribers per state and year.

Output

One row per qualifying prescriber, state, and year
Columns: year, state, prescriber_id, prescriber_name, prescription_count, and prescriber_rank
Sort by year, state, prescriber_rank, and prescriber_id

Schema

prescriptions

Column	Type	Description
prescription_idPK	INT	Unique prescription record identifier
prescriber_id	INT	Prescriber who issued the prescription
medication_id	INT	Medication associated with the prescription
prescribed_at	DATE	Date the prescription was issued

prescribers

Column	Type	Description
prescriber_idPK	INT	Unique prescriber identifier
prescriber_name	VARCHAR(100)	Prescriber's full name
state	VARCHAR(2)	US state associated with the prescriber

medications

Column	Type	Description
medication_idPK	INT	Unique medication identifier
medication_name	VARCHAR(100)	Medication product name

Your question is Top Prescribers SQL by State. Start with the requirements and the three 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