Deal stage history

1-- core.hubspot_v2.deal_stage_history
2-- Every stage each deal has been in, when it entered, when it left and how long it
3-- stayed. Grain: one row per deal x stage entry (a deal that goes back to a stage
4-- it already visited gets a second row).
5-- Depends on: staging.hubspot_v2.deal_property_history, .pipeline_stage,
6--             .pipeline, core.hubspot_v2.deal_pipeline
7--
8-- Answers what deal_pipeline cannot: how long deals sit in each stage, which stages
9-- they skip, and where they stall. It is the data behind HubSpot's "Deal stage
10-- funnel" and "Time spent in deal stage" reports.
11--
12-- HOW IT IS BUILT. Each change to the `dealstage` property is a row in the
13-- property history. A stage entry lasts until the next change; the latest entry is
14-- still running and has exited_at NULL. Consecutive rows with the same stage (a
15-- re-save that wrote the same value) are collapsed into one entry, so they do not
16-- show up as zero-day visits.
17--
18-- STAGE LABELS are looked up by stage id across all deal pipelines, not only
19-- the deal's current one. HubSpot stage ids are unique within a portal, so a deal
20-- that moved from one pipeline to another keeps readable labels for the stages
21-- it passed through before the move, and `stage_pipeline_id` says which pipeline
22-- each entry belonged to. moved_backwards only compares stages in the same
23-- pipeline; a move to another pipeline is never called backwards.
24--
25-- REQUIRES the deal_property_history stream. Without it this model is empty.
26-- Deals created before HubSpot started recording history for your portal start
27-- at the first change it has.
28
29WITH changes AS (
30    SELECT
31        hubspot_portal,
32        deal_id,
33        value      AS stage_id,
34        changed_at,
35        LAG(value) OVER (
36            PARTITION BY hubspot_portal, deal_id ORDER BY changed_at
37        )          AS previous_stage_id
38    FROM {{staging.hubspot_v2.deal_property_history}}
39    WHERE property = 'dealstage'
40      AND value IS NOT NULL