Cleaning Messy Datasets | Dataford Interview Questions

Walk us through a time you had to clean a messy dataset before analysis.

Using the supplied appointment and patient tables, write a PostgreSQL query that produces analysis-ready appointment records. Standardize inconsistent dates, statuses, NHS numbers, and durations, remove duplicate appointment records, and exclude records that cannot be validated.

Output

One row per valid, deduplicated appointment, with patient_id, patient_name, appointment_date, appointment_type, status, and duration_minutes.
Include only records with a matched patient, valid date, and recognized status. Order by patient_id, appointment_date, and appointment_type.

Schema

raw_appointments

Column	Type	Description
raw_idPK	INT	Identifier for the raw appointment record
nhs_number_text	VARCHAR(30)	Raw NHS number, potentially containing spaces or invalid characters
appointment_date_text	VARCHAR(20)	Appointment date in an inconsistent text format
appointment_type	VARCHAR(50)	Type of appointment
status_text	VARCHAR(30)	Raw appointment status
duration_text	VARCHAR(30)	Appointment duration stored as text
source_updated_at	TIMESTAMP	Timestamp used to select the latest duplicate record

patient_directory

Column	Type	Description
patient_idPK	INT	Canonical patient identifier
nhs_number	VARCHAR(20)	Validated NHS number
patient_name	VARCHAR(100)	Patient display name

Your question is Cleaning Messy Datasets. 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