You’re working on the analytics team for a large healthcare provider network operating across 40 clinics. The network processes millions of outpatient visits per year, and care coordinators are measured on how quickly patients return for follow-up after an initial appointment (a key driver for quality metrics and reimbursement).
A common operational question is: how long does it take a patient to come back for their second visit after their first? This metric is used to identify patients who may be falling out of care, and to evaluate whether certain clinics or service lines have longer follow-up delays.
Write a SQL query that returns, for each patient, the elapsed time between their first and second recorded visit.
visit_start_ts.patient_id, identify the chronologically first and chronologically second visit by visit_start_ts.patient_id ascending.patient_visits| Column | Type | Description |
|---|---|---|
| visit_id | BIGINT | Unique identifier for the visit record |
| patient_id | BIGINT | Unique identifier for the patient |
| clinic_id | INT | Clinic where the visit occurred |
| visit_start_ts | TIMESTAMP | Visit start timestamp (used for sequencing visits) |
| visit_type | VARCHAR(50) | Visit type (e.g., annual_physical, follow_up, urgent_care) |
patient_visits| visit_id | patient_id | clinic_id | visit_start_ts | visit_type |
|---|---|---|---|---|
| 9001 | 101 | 12 | 2025-01-03 09:15:00 | annual_physical |
| 9002 | 101 | 12 | 2025-01-10 14:30:00 | follow_up |
| 9003 | 102 | 18 | 2025-02-01 08:00:00 | urgent_care |
| 9004 | 102 | 18 | 2025-02-01 16:00:00 | follow_up |
| 9005 | 103 | 12 | 2025-03-05 11:00:00 | annual_physical |
| patient_id | first_visit_ts | second_visit_ts | hours_between_first_and_second |
|---|---|---|---|
| 101 | 2025-01-03 09:15:00 | 2025-01-10 14:30:00 | 173.25 |
| 102 | 2025-02-01 08:00:00 | 2025-02-01 16:00:00 | 8.00 |
Note: Patient
103is excluded because they have only one visit in the sample data.