Quiet time between incidents

window functions

Quiet time between incidents

Datadog SQL Interview Question

Datadog tracks how long a service stays quiet between pages, because a service that fails every two hours needs a rewrite while one that fails every two months needs a runbook. The gap is measured from one incident starting to the next one starting on the same service.

Return the service, the incident_id, when it began as started_at, and the hours since the previous incident on that service began as hours_since_previous, rounded to 2 decimal places. The first incident on a service has nothing before it, so leave its gap empty. Sort by service and then by started_at.

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

serviceincident_idstarted_athours_since_previous
billing-worker60112024-10-01 12:00:00NULL
billing-worker60122024-10-01 12:30:000.5
billing-worker60132024-10-01 15:40:003.17
checkout-api60012024-10-01 02:10:00NULL
checkout-api60022024-10-01 03:05:000.92
checkout-api60032024-10-01 06:00:002.92

Explanation

The first incident on each service has nothing before it, so its gap is empty. On the billing worker the second began 30 minutes after the first, which is 0.5 hours, and the third came 3.17 hours after that.

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