Issue cycle time

1-- core.linear.issue_cycle_time
2-- Lead time, cycle time and triage time for every completed issue.
3-- Recreates the cycle time and lead time charts in Linear Insights.
4-- Grain: completed issue.
5--
6--   lead_time_days   created   -> completed
7--   cycle_time_days  started   -> completed (NULL if the issue skipped "started")
8--   triage_time_days entered triage -> left triage (NULL if never in triage)
9--
10-- Times are in fractional days. Report medians or percentiles rather than means:
11-- one issue that sat in the backlog for a year will dominate an average.
12
13SELECT
14    i.issue_id,
15    i.identifier,
16    i.title,
17    i.team_id,
18    i.team_name,
19    i.project_id,
20    i.project_name,
21    i.cycle_id,
22    i.cycle_number,
23    i.assignee_id,
24    i.assignee_name,
25    i.priority,
26    i.priority_label,
27    i.estimate,
28    i.parent_issue_id IS NOT NULL                                             AS is_sub_issue,
29    i.created_at,
30    i.started_at,
31    i.completed_at,
32    DATE(i.completed_at)                                                      AS completed_date,
33    ROUND(TIMESTAMP_DIFF(i.completed_at, i.created_at, SECOND) / 86400, 2)    AS lead_time_days,
34    ROUND(TIMESTAMP_DIFF(i.completed_at, i.started_at, SECOND) / 86400, 2)    AS cycle_time_days,
35    ROUND(TIMESTAMP_DIFF(i.triaged_at, i.started_triage_at, SECOND) / 86400, 2) AS triage_time_days,
36    i.due_date,
37    i.due_date IS NOT NULL AND DATE(i.completed_at) > i.due_date              AS completed_after_due_date
38FROM {{staging.linear.issue}} i
39WHERE i.completed_at IS NOT NULL
40  AND i.state_type = 'completed'