Open backlog

1-- core.linear.open_backlog
2-- Everything still open, by team, workflow state, priority and assignee, with
3-- age and overdue counts. A snapshot as of the last sync.
4-- Grain: team x state x priority x assignee.
5--
6-- Open means state_type in triage, backlog, unstarted or started. Age is days
7-- since the issue was created, in UTC. Average age is carried as a sum and a
8-- count so it can be re-aggregated.
9
10SELECT
11    i.team_id,
12    i.team_name,
13    i.state_name,
14    i.state_type,
15    i.priority,
16    i.priority_label,
17    i.assignee_id,
18    i.assignee_name,
19    COUNT(*)                                                          AS open_issues,
20    COUNTIF(i.estimate IS NULL)                                       AS unestimated_issues,
21    SUM(COALESCE(i.estimate, 0))                                      AS open_points,
22    COUNTIF(i.due_date < CURRENT_DATE())                              AS overdue_issues,
23    COUNTIF(i.sla_breaches_at < CURRENT_TIMESTAMP())                  AS sla_breached_issues,
24    COUNTIF(i.cycle_id IS NOT NULL)                                   AS issues_in_a_cycle,
25    SUM(DATE_DIFF(CURRENT_DATE(), DATE(i.created_at), DAY))           AS age_days_total,
26    ROUND(AVG(DATE_DIFF(CURRENT_DATE(), DATE(i.created_at), DAY)), 1) AS avg_age_days,
27    MAX(DATE_DIFF(CURRENT_DATE(), DATE(i.created_at), DAY))           AS oldest_age_days
28FROM {{staging.linear.issue}} i
29WHERE i.state_type IN ('triage', 'backlog', 'unstarted', 'started')
30  AND i.archived_at IS NULL
31GROUP BY 1, 2, 3, 4, 5, 6, 7, 8