Open incidents

1-- core.pager_duty.open_incidents
2-- Every incident that is not resolved yet, with its age: the Incidents page filtered
3-- to Open, for a backlog dashboard or an alert on incidents left acknowledged for
4-- days.
5--
6-- Grain: one row per open incident (current state as of the last sync).
7-- Depends on: staging.pager_duty.raw_incident
8--
9-- Status comes from the analytics table, which is updated whenever an incident
10-- changes, so an old incident that was resolved recently drops out of this model on the
11-- next sync.
12
13SELECT
14    incident_id,
15    incident_number,
16    title,
17    status,
18    urgency,
19    priority_name,
20    is_major,
21    service_id,
22    service_name,
23    team_id,
24    team_name,
25    escalation_policy_name,
26    created_at,
27    TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created_at, MINUTE) / 60.0      AS age_hours,
28    first_acknowledged_user_id,
29    seconds_to_first_ack IS NOT NULL                                    AS has_been_acknowledged,
30    assignment_count,
31    escalation_count,
32    total_interruptions
33FROM {{staging.pager_duty.raw_incident}}
34WHERE status != 'resolved'