SQL for Consecutive Treatment Gaps | Dataford Interview Questions

Your question is SQL for Consecutive Treatment Gaps. Start with the requirements and the one table 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

Verily’s clinical research workflows need to identify periods when a patient had no recorded treatment activity. Given a patient event log, write a PostgreSQL query that finds consecutive treatment events separated by more than 90 days.

Requirements

Consider only rows where event_type = 'treatment' and event_date is not NULL.
Compare each treatment event with the immediately preceding treatment event for the same patient. Other event types, such as visits and lab results, must not reset the treatment sequence.
Return the patient, the start and end dates of each qualifying gap, and the number of elapsed days.
Include only gaps strictly greater than 90 days and order the results by patient and gap start date.

Schema

patient_event_log

Column	Type	Description
event_idPK	INT	Unique clinical event identifier
patient_id	INT	Identifier of the patient associated with the event
event_type	VARCHAR(30)	Clinical event classification, such as treatment, visit, or lab
event_date	DATE	Date on which the clinical event occurred

Your question is SQL for Consecutive Treatment Gaps. Start with the requirements and the one table 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.

Requirements

Consider only rows where event_type = 'treatment' and event_date is not NULL.
Compare each treatment event with the immediately preceding treatment event for the same patient. Other event types, such as visits and lab results, must not reset the treatment sequence.
Return the patient, the start and end dates of each qualifying gap, and the number of elapsed days.
Include only gaps strictly greater than 90 days and order the results by patient and gap start date.

Schema

patient_event_log

Column	Type	Description
event_idPK	INT	Unique clinical event identifier
patient_id	INT	Identifier of the patient associated with the event
event_type	VARCHAR(30)	Clinical event classification, such as treatment, visit, or lab
event_date	DATE	Date on which the clinical event occurred