Case volume daily

1-- core.sprout_social.case_volume_daily
2-- Cases opened and closed per day, with the median and 90th percentile time to
3-- close: Sprout's case volume and resolution trend.
4--
5-- Grain: date_day (UTC) x group_id x queue_id x case_type x case_priority.
6-- Depends on: core.sprout_social.case_resolution
7--
8-- Opened is counted on the creation date, closed on the last close date, so the two
9-- columns describe different cases. A case reopened and closed again is counted
10-- once, on its latest close. Percentiles are per day; do not average them across
11-- days, recompute from case_resolution instead.
12
13WITH events AS (
14    SELECT created_date AS date_day, customer_id, group_id, group_name, queue_id,
15           case_type, case_priority, 1 AS opened, 0 AS closed, CAST(NULL AS FLOAT64) AS hours_to_close
16    FROM {{core.sprout_social.case_resolution}}
17    WHERE created_date IS NOT NULL
18
19    UNION ALL
20
21    SELECT closed_date AS date_day, customer_id, group_id, group_name, queue_id,
22           case_type, case_priority, 0 AS opened, 1 AS closed, hours_to_close
23    FROM {{core.sprout_social.case_resolution}}
24    WHERE is_closed AND closed_date IS NOT NULL
25)
26
27SELECT
28    date_day,
29    customer_id,
30    group_id,
31    ANY_VALUE(group_name)                                    AS group_name,
32    queue_id,
33    case_type,
34    case_priority,
35    SUM(opened)                                              AS cases_opened,
36    SUM(closed)                                              AS cases_closed,
37    APPROX_QUANTILES(hours_to_close, 100 IGNORE NULLS)[SAFE_OFFSET(50)] AS median_hours_to_close,
38    APPROX_QUANTILES(hours_to_close, 100 IGNORE NULLS)[SAFE_OFFSET(90)] AS p90_hours_to_close
39FROM events
40GROUP BY date_day, customer_id, group_id, queue_id, case_type, case_priority