Issue flow

1-- core.linear.issue_flow
2-- Issues created, started, completed and canceled per day, team and assignee.
3-- The created-vs-completed chart from Linear Insights, as a table.
4-- Grain: day (UTC) x team x assignee.
5--
6-- Each issue contributes to up to four days: the day it was created, started,
7-- completed and canceled. Team and assignee are the issue's CURRENT values: an
8-- issue moved between teams counts all its events under the team it is in now.
9-- Linear does not expose state history through this connector.
10
11WITH events AS (
12    SELECT team_id, team_name, assignee_id, assignee_name, DATE(created_at) AS day,
13           1 AS created, 0 AS started, 0 AS completed, 0 AS canceled, 0.0 AS points_completed
14    FROM {{staging.linear.issue}} WHERE created_at IS NOT NULL
15    UNION ALL
16    SELECT team_id, team_name, assignee_id, assignee_name, DATE(started_at),
17           0, 1, 0, 0, 0.0
18    FROM {{staging.linear.issue}} WHERE started_at IS NOT NULL
19    UNION ALL
20    SELECT team_id, team_name, assignee_id, assignee_name, DATE(completed_at),
21           0, 0, 1, 0, COALESCE(estimate, 0)
22    FROM {{staging.linear.issue}} WHERE completed_at IS NOT NULL
23    UNION ALL
24    SELECT team_id, team_name, assignee_id, assignee_name, DATE(canceled_at),
25           0, 0, 0, 1, 0.0
26    FROM {{staging.linear.issue}} WHERE canceled_at IS NOT NULL
27)
28
29SELECT
30    day,
31    team_id,
32    ANY_VALUE(team_name)        AS team_name,
33    assignee_id,
34    ANY_VALUE(assignee_name)    AS assignee_name,
35    SUM(created)                AS issues_created,
36    SUM(started)                AS issues_started,
37    SUM(completed)              AS issues_completed,
38    SUM(canceled)               AS issues_canceled,
39    SUM(points_completed)       AS points_completed
40FROM events