Active Unfilled Prescriptions Query | Dataford Interview Questions

Write a MySQL query to retrieve all active prescriptions for a patient at Gifthealth that have not yet been filled.

Use the provided schema and assume the target patient_id is 101. Treat any matching row in prescription_fills as evidence that a prescription has been filled.

Output

Return one row per qualifying prescription with prescription_id, patient_id, medication_name, and prescribed_date.
Include only active, unfilled prescriptions for patient 101.
Order by prescribed_date ascending, then prescription_id ascending.

Schema

prescriptions

Column	Type	Description
prescription_idPK	INT	Unique prescription identifier
patient_id	INT	Gifthealth patient identifier
prescription_status	VARCHAR(20)	Current prescription status
medication_name	VARCHAR(120)	Name of the prescribed medication
prescribed_date	DATE	Date the prescription was issued
quantity	INT	Quantity prescribed
expires_on	DATE	Prescription expiration date

prescription_fills

Column	Type	Description
fill_idPK	INT	Unique fill record identifier
prescription_id	INT	Prescription associated with the fill
filled_at	TIMESTAMP	Timestamp when the prescription was filled
fill_status	VARCHAR(20)	Status of the fill transaction
pharmacy_name	VARCHAR(120)	Pharmacy that processed the fill

Your question is Active Unfilled Prescriptions Query. 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