Responder load daily

1-- core.pager_duty.responder_load_daily
2-- How much incident work lands on each responder: incidents assigned to them,
3-- acknowledged by them and resolved by them. The per-person view of PagerDuty's
4-- Responder report, for spotting the people carrying the on-call load.
5--
6-- Grain: one row per incident creation day (UTC) x user.
7-- Depends on: staging.pager_duty.raw_incident, .raw_incident_assigned_user,
8--             .raw_incident_acknowledged_user, .user
9--
10-- An incident assigned to three people counts once for each of them, so this model
11-- does not sum to incident_response_daily across users; that is the point of it.
12--
13-- The `_with_off_hour_interruptions` and `_with_sleep_hour_interruptions` columns
14-- count assigned incidents that interrupted someone outside working or sleeping
15-- hours. PagerDuty reports interruptions per incident, not per responder, so the
16-- person interrupted may have been a colleague on the same incident.
17
18WITH involvement AS (
19    SELECT incident_id, user_id, 'assigned' AS role FROM {{staging.pager_duty.raw_incident_assigned_user}}
20    UNION ALL
21    SELECT incident_id, user_id, 'acknowledged' AS role FROM {{staging.pager_duty.raw_incident_acknowledged_user}}
22    UNION ALL
23    SELECT incident_id, resolved_by_user_id AS user_id, 'resolved' AS role
24    FROM {{staging.pager_duty.raw_incident}}
25    WHERE resolved_by_user_id IS NOT NULL
26)
27
28SELECT
29    i.created_date                                                  AS date_day,
30    v.user_id,
31    ANY_VALUE(u.user_name)                                          AS user_name,
32    ANY_VALUE(u.time_zone)                                          AS user_time_zone,
33
34    COUNT(DISTINCT IF(v.role = 'assigned', v.incident_id, NULL))    AS incidents_assigned,
35    COUNT(DISTINCT IF(v.role = 'acknowledged', v.incident_id, NULL)) AS incidents_acknowledged,
36    COUNT(DISTINCT IF(v.role = 'resolved', v.incident_id, NULL))    AS incidents_resolved,
37    COUNT(DISTINCT IF(v.role = 'assigned' AND i.urgency = 'high', v.incident_id, NULL))
38                                                                    AS high_urgency_incidents_assigned,
39    COUNT(DISTINCT IF(v.role = 'assigned' AND i.off_hour_interruptions > 0, v.incident_id, NULL))
40                                                                    AS incidents_assigned_with_off_hour_interruptions,