SQL for Consecutive Treatment Gaps

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

1. Consider only rows where event_type = 'treatment' and event_date is not NULL.
2. 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.
3. Return the patient, the start and end dates of each qualifying gap, and the number of elapsed days.
4. Include only gaps strictly greater than 90 days and order the results by patient and gap start date.