Find Data Inconsistencies in SQL | Dataford Interview Questions

Given a specific dataset, how would you identify logical inconsistencies or missing data points in SQL for PharmaACE?

Write a query against the supplied study visit and measurement data. Identify expected visits with missing actual records, dates outside the allowed seven-day window, non-completed statuses, missing measurements, or measurements without a non-null result.

Output

One row per expected visit with an issue
Columns: expected_visit_id, patient_id, expected_date, actual_visit_id, issue_type, and issue_detail
Exclude records without issues and order by expected_date, then expected_visit_id

Schema

expected_visits

Column	Type	Description
expected_visit_idPK	INT	Unique identifier for an expected study visit
patient_id	VARCHAR(20)	Study patient identifier
expected_date	DATE	Planned visit date

actual_visits

Column	Type	Description
actual_visit_idPK	INT	Unique identifier for a recorded visit
expected_visit_id	INT	Expected visit associated with the recorded visit
visit_date	DATE	Date on which the visit occurred
status	VARCHAR(20)	Recorded visit status

visit_measurements

Column	Type	Description
measurement_idPK	INT	Unique identifier for a measurement
actual_visit_id	INT	Recorded visit associated with the measurement
measurement_name	VARCHAR(40)	Name of the collected measurement
result_value	DECIMAL(8,2)	Numeric measurement result

Your question is Find Data Inconsistencies in SQL. 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