SQL for Compliance Extraction | Dataford Interview Questions

Your question is SQL for Compliance Extraction. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

ERM Digital stores environmental, health, safety, and regulatory obligations across client facilities. Write a PostgreSQL query that produces an operational extract of active compliance records requiring attention by June 30, 2025.

Requirements

Return records with status Open or Overdue whose due date is on or before June 30, 2025, or whose due date is missing.
Use a LEFT JOIN so records remain visible when their facility or facility attributes are missing.
Replace missing facility names, regions, responsible people, and due dates with readable labels. Classify each record as Overdue, Due by cutoff, or Missing due date.
Order records by due date ascending, placing records without due dates last.

Schema

compliance_records

Column	Type	Description
record_idPK	INT	Unique compliance record identifier
facility_id	INT	Referenced facility identifier
requirement_name	VARCHAR(120)	Name of the compliance obligation
status	VARCHAR(20)	Current compliance record status
due_date	DATE	Required completion date
responsible_person	VARCHAR(100)	Assigned compliance owner
created_date	DATE	Date the record was created

facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(120)	Facility display name
region	VARCHAR(60)	Operating region
erm_site_code	VARCHAR(30)	ERM Digital site code