Merged outage windows

window functions

Merged outage windows

Datadog SQL Interview Question

Datadog reports uptime to customers under a contract, so the number has to be minutes the service was actually down. Adding incident durations double counts every minute two alerts overlapped, which overstates the outage and can trigger a refund that was never owed.

Return one row per merged window: the service, the time it began as window_start, the time the service was finally healthy as window_end, and how many incidents it covers as incidents. A new window begins only when an incident starts after every earlier one on that service has been resolved. Sort by service and then by window_start.

Asked of

  • Data Analyst
  • Analytics Engineer
  • Data Engineer
  • Data Scientist

datadog_incidentsTable20 rows

Column NameType
incident_idBIGINT
serviceVARCHAR
started_atTIMESTAMP
resolved_atTIMESTAMP
severityVARCHAR

datadog_incidentsExample Input

incident_idservicestarted_atresolved_atseverity
6001checkout-api2024-10-01 02:10:002024-10-01 03:40:00SEV2
6002checkout-api2024-10-01 03:05:002024-10-01 04:25:00SEV3
6003checkout-api2024-10-01 06:00:002024-10-01 06:45:00SEV1
6011billing-worker2024-10-01 12:00:002024-10-01 12:30:00SEV3
6012billing-worker2024-10-01 12:30:002024-10-01 13:15:00SEV2
6013billing-worker2024-10-01 15:40:002024-10-01 16:55:00SEV1

Example Output

servicewindow_startwindow_endincidents
billing-worker2024-10-01 12:00:002024-10-01 13:15:002
billing-worker2024-10-01 15:40:002024-10-01 16:55:001
checkout-api2024-10-01 02:10:002024-10-01 04:25:002
checkout-api2024-10-01 06:00:002024-10-01 06:45:001

Explanation

The two checkout incidents run into each other, so they merge into one window from 2:10 to 4:25 covering two incidents. The later one at 6:00 stands on its own. On the billing worker one incident ends exactly as the next begins, and since the service was never healthy in between those merge too.

The example above is a small slice of the data. Your query runs against the full tables.