Status changes

1-- core.monday.status_changes
2-- Every change to a status column, with the label before and after and how long
3-- the item sat in the previous label.
4-- Grain: one row per status change event (item x status column x change time).
5--
6-- Status label text is only available here. The item's column value stores the
7-- label index, and board column settings are not synced, so the
8-- activity log is the one place where "Working on it" or "Done" appears as text.
9-- `is_current_label` marks the latest change per item and column, which is the
10-- item's current status as long as the change happened inside the activity log
11-- retention window.
12--
13-- `hours_in_previous_label` is NULL for the first change seen per item and
14-- column: the time the item entered that label is outside the synced log.
15-- monday names the status column type "color" in activity log payloads.
16
17WITH changes AS (
18    SELECT
19        a.activity_log_id,
20        a.board_id,
21        JSON_VALUE(a.data_json, '$.pulse_id')                 AS item_id,
22        JSON_VALUE(a.data_json, '$.pulse_name')               AS item_name_at_change,
23        JSON_VALUE(a.data_json, '$.column_id')                AS column_id,
24        JSON_VALUE(a.data_json, '$.column_title')             AS column_title,
25        JSON_VALUE(a.data_json, '$.previous_value.label.text') AS from_label,
26        JSON_VALUE(a.data_json, '$.value.label.text')         AS to_label,
27        a.user_id,
28        a.created_at                                          AS changed_at
29    FROM {{staging.monday.activity_log}} a
30    WHERE a.event = 'update_column_value'
31      AND LOWER(JSON_VALUE(a.data_json, '$.column_type')) IN ('color', 'status')
32),
33
34sequenced AS (
35    SELECT
36        *,
37        LAG(changed_at) OVER (PARTITION BY item_id, column_id ORDER BY changed_at, activity_log_id) AS previous_changed_at,
38        ROW_NUMBER() OVER (PARTITION BY item_id, column_id ORDER BY changed_at DESC, activity_log_id DESC) AS recency
39    FROM changes
40)