Incident response daily

1-- core.pager_duty.incident_response_daily
2-- Incident volume and response times per service and team: PagerDuty's Service
3-- Performance and Team Performance reports, by day. Mean time to acknowledge (MTTA),
4-- mean time to resolve (MTTR), escalations, auto-resolves and the interruption load
5-- on responders.
6--
7-- Grain: one row per incident creation day (UTC) x service x team x urgency x priority.
8-- Depends on: staging.pager_duty.raw_incident
9--
10-- MEANS ARE SHIPPED AS SUMS AND COUNTS. Every timing comes with its total seconds
11-- and the number of incidents it covers, and the mean in the same row is the one
12-- divided by the other. To roll up to a week or a whole team, sum both and divide
13-- again; averaging the mean column weights a day with one incident the same as a
14-- day with a hundred.
15--
16-- Incidents are dated on creation, as PagerDuty's reports are. An incident
17-- resolved on a later day still counts on the day it started, so the most recent
18-- days have open incidents in them and their MTTR rises as those resolve.
19
20SELECT
21    created_date                                                AS date_day,
22    service_id,
23    ANY_VALUE(service_name)                                     AS service_name,
24    team_id,
25    ANY_VALUE(team_name)                                        AS team_name,
26    urgency,
27    priority_name,
28
29    COUNT(*)                                                    AS incidents,
30    COUNTIF(is_major)                                           AS major_incidents,
31    COUNTIF(status = 'resolved')                                AS resolved_incidents,
32    COUNTIF(status != 'resolved')                               AS open_incidents,
33    COUNTIF(is_auto_resolved)                                   AS auto_resolved_incidents,
34
35    -- Time to acknowledge.
36    COUNTIF(seconds_to_first_ack IS NOT NULL)                   AS acknowledged_incidents,
37    SUM(seconds_to_first_ack)                                   AS total_seconds_to_first_ack,
38    ROUND(SAFE_DIVIDE(SUM(seconds_to_first_ack),
39                      COUNTIF(seconds_to_first_ack IS NOT NULL)) / 60, 2) AS mtta_minutes,
40