Handling Missing Longitudinal Data | Dataford Interview Questions

How do you handle missing or erroneous data points in a longitudinal research study?

For this SQL exercise, treat a missing point as a scheduled visit without a measurement. Treat a point as erroneous when its value is NULL, outside the range 0 to 100, or recorded before the scheduled visit.

Output

Return one row per missing or erroneous scheduled visit.
Include participant_id, participant_name, scheduled_visit_id, scheduled_date, issue_type, observed_value, and recorded_at.
Sort by participant, scheduled date, and scheduled visit ID.

Schema

participants

Column	Type	Description
participant_idPK	INT	Unique study participant identifier
participant_name	VARCHAR(100)	Participant display name

scheduled_visits

Column	Type	Description
scheduled_visit_idPK	INT	Unique scheduled visit identifier
participant_id	INT	Participant expected at the visit
scheduled_date	DATE	Date on which the measurement was expected

measurements

Column	Type	Description
measurement_idPK	INT	Unique measurement identifier
scheduled_visit_id	INT	Scheduled visit associated with the measurement
measurement_value	NUMERIC(6,2)	Observed study value expected to be between 0 and 100
recorded_at	TIMESTAMP	Timestamp when the measurement was recorded

Your question is Handling Missing Longitudinal Data. 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