Incidents that overlapped
hardIncidents that overlapped
Datadog SQL Interview Question
When two Datadog alerts fire on one service at the same time, they are usually one failure seen twice, and paging two engineers for it is how an on-call rotation burns out. Before the alerts can be merged, the review needs the pairs that genuinely overlapped. Two incidents that merely touch, one ending exactly as the next begins, are separate events.
Return each overlapping pair once: the service, the lower incident id of the pair as earlier_incident, and the higher one as later_incident.
Asked of
- Data Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
datadog_incidentsTable20 rows
| Column Name | Type |
|---|---|
| incident_id | BIGINT |
| service | VARCHAR |
| started_at | TIMESTAMP |
| resolved_at | TIMESTAMP |
| severity | VARCHAR |
datadog_incidentsExample Input
| incident_id | service | started_at | resolved_at | severity |
|---|---|---|---|---|
| 6001 | checkout-api | 2024-10-01 02:10:00 | 2024-10-01 03:40:00 | SEV2 |
| 6002 | checkout-api | 2024-10-01 03:05:00 | 2024-10-01 04:25:00 | SEV3 |
| 6003 | checkout-api | 2024-10-01 06:00:00 | 2024-10-01 06:45:00 | SEV1 |
| 6011 | billing-worker | 2024-10-01 12:00:00 | 2024-10-01 12:30:00 | SEV3 |
| 6012 | billing-worker | 2024-10-01 12:30:00 | 2024-10-01 13:15:00 | SEV2 |
| 6013 | billing-worker | 2024-10-01 15:40:00 | 2024-10-01 16:55:00 | SEV1 |
Example Output
| service | earlier_incident | later_incident |
|---|---|---|
| checkout-api | 6001 | 6002 |
Explanation
In the example, 6001 ran from 2:10 to 3:40 and 6002 started at 3:05, before the first was resolved, so they overlap. The billing worker pair does not: one ended at 12:30 and the next began at 12:30, which is touching rather than overlapping.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Datadog
Difficulty
hard
Topic
joins
Language
SQL
Your query
Starting the SQL engine...