Monthly Abnormal Results by Facility | Dataford Interview Questions

Can you explain how you would optimize a database for performance at 3Y Health?

For this hands-on SQL exercise, use the supplied PostgreSQL schema to produce the reporting result and explain performance improvements. Return only qualifying facilities and months.

Output

One row per facility and calendar month.
Columns: facility_id, facility_name, report_month, and abnormal_result_count.
Include active patients' abnormal results from January through March 2025, keeping facility-month groups with at least two results.
Order by report_month ascending, then facility_id ascending.

Schema

facilities

Column	Type	Description
facility_idPK	INT	Facility identifier
facility_name	VARCHAR(100)	Facility name

patients

Column	Type	Description
patient_idPK	INT	Patient identifier
facility_id	INT	Primary facility for the patient
status	VARCHAR(20)	Patient status

lab_results

Column	Type	Description
result_idPK	INT	Lab result identifier
patient_id	INT	Patient associated with the result
test_name	VARCHAR(80)	Name of the laboratory test
resulted_at	TIMESTAMP	Timestamp when the result was recorded
is_abnormal	BOOLEAN	Whether the result was outside the normal range

Your question is Monthly Abnormal Results by Facility. 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