On-Call Handover Audit
Pager Duty
Problem Statement
Reliability is the core of PagerDuty. A critical failure point in incident management is the "coverage gap"—a period where one engineer has finished their shift, but the next engineer has not yet started.
Write a query to find all instances where there was a gap of more than 5 minutes between two consecutive shifts in the same schedule.
Rules:
- Output columns: schedule_id, shift_end, next_shift_start, gap_duration_minutes.
- A gap is defined as: next_shift_start - current_shift_end.
- Only include gaps where the duration is strictly greater than 5 minutes.
- Results must be sorted by schedule_id ascending, then by shift_end ascending.
- Assume shifts within a schedule do not overlap.
Tests your understanding of
Lead/Lag Functions, Time Series and Interval Analysis
Input Tables
oncall_shifts
| shift_id(INTEGER) | schedule_id(INTEGER) | user_id(INTEGER) | start_time(TIMESTAMP) | end_time(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 501 | 10 | 2024-03-01 00:00:00 | 2024-03-01 08:00:00 |
| 2 | 501 | 11 | 2024-03-01 08:06:00 | 2024-03-01 16:00:00 |
| 3 | 501 | 12 | 2024-03-01 16:02:00 | 2024-03-02 00:00:00 |
| 4 | 502 | 20 | 2024-03-01 00:00:00 | 2024-03-01 12:00:00 |
| 5 | 502 | 21 | 2024-03-01 12:15:00 | 2024-03-02 00:00:00 |
| 6 | 503 | 30 | 2024-03-01 00:00:00 | 2024-03-01 08:00:00 |
Expected Output
| schedule_id(INTEGER) | shift_end(TIMESTAMP) | next_shift_start(TIMESTAMP) | gap_duration_minutes(INTEGER) |
|---|---|---|---|
| 501 | 2024-03-01 08:00:00 | 2024-03-01 08:06:00 | 6 |
| 502 | 2024-03-01 12:00:00 | 2024-03-01 12:15:00 | 15 |
Tags
HardLead/Lag FunctionsTime SeriesInterval Analysis
40-50 min
24%