Ticket volume by day

1-- core.gorgias.ticket_volume_by_day
2-- Tickets created, closed and left open per day: the "Overview" volume charts.
3-- Grain: day x gorgias_account x team x channel.
4-- Depends on: core.gorgias.ticket_lifecycle
5--
6-- Built on a day spine, so a quiet team or channel returns zeros.
7--
8-- tickets_closed counts tickets on their LATEST close. Gorgias' "Tickets closed"
9-- counts close events, so a ticket closed, reopened and closed again counts twice
10-- there and once here. The connector can sync every close event into an
11-- `event_ticket_closed` table if you enable that event type; it is not required
12-- by these templates.
13--
14-- OPEN BACKLOG is running created minus running closed, each ticket in the team
15-- and channel it has now. The last day equals today's open ticket count; earlier
16-- days are a reconstruction, because a reopened ticket counts as open from
17-- creation to its last close. Snoozed tickets are open in Gorgias and count here.
18
19WITH tickets AS (
20    SELECT
21        gorgias_account,
22        IFNULL(team_id, '') AS team_key,
23        team_id,
24        team_name,
25        channel,
26        created_date,
27        resolved_date
28    FROM {{core.gorgias.ticket_lifecycle}}
29),
30
31dims AS (
32    SELECT
33        gorgias_account,
34        team_key,
35        channel,
36        ANY_VALUE(team_id)   AS team_id,
37        ANY_VALUE(team_name) AS team_name,
38        MIN(created_date)    AS first_day
39    FROM tickets
40    GROUP BY 1, 2, 3