Business Context
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.
Task
Write a SQL query that returns, for each patient, the elapsed time between their first and second recorded visit.
Requirements
- Consider only visits with a non-null
visit_start_ts.
- For each
patient_id, identify the chronologically first and chronologically second visit by visit_start_ts.
- Return one row per patient only if they have at least two visits.
- Output the timestamps of the first and second visits, plus the elapsed time between them in hours (as a decimal).
- Order results by
patient_id ascending.
Table Definitions
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) |
Sample Data
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 |
Expected Output
| 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 103 is excluded because they have only one visit in the sample data.
