Pipeline snapshot

1-- core.go_high.pipeline_snapshot
2-- The HighLevel Opportunities board as numbers: how many deals and how much value sit in
3-- each stage of each pipeline right now, and how long they have been there.
4--
5-- Grain: one row per location x pipeline x stage x status, as of the last sync.
6-- Depends on: staging.go_high.opportunity, .pipeline, .pipeline_stage
7--
8-- Open deals are the board. Won, lost and abandoned deals stay attached to the stage they
9-- were in when they closed, so they are kept as separate rows by `status` rather than
10-- mixed into the open value.
11-- `avg_days_in_stage` uses the last stage change, so it is the age of the deal in its
12-- current stage, measured at sync time.
13
14SELECT
15    o.location_id,
16    o.pipeline_id,
17    p.pipeline_name,
18    o.pipeline_stage_id,
19    s.stage_name,
20    s.stage_position,
21    o.status,
22
23    COUNT(*)                                                    AS opportunities,
24    SUM(o.monetary_value)                                       AS total_value,
25    AVG(o.monetary_value)                                       AS avg_value,
26    AVG(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),
27        COALESCE(o.last_stage_change_at, o.created_at), HOUR) / 24) AS avg_days_in_stage,
28    COUNTIF(o.assigned_user_id IS NULL)                         AS unassigned
29FROM {{staging.go_high.opportunity}} o
30LEFT JOIN {{staging.go_high.pipeline}} p        ON p.pipeline_id = o.pipeline_id
31LEFT JOIN {{staging.go_high.pipeline_stage}} s  ON s.pipeline_stage_id = o.pipeline_stage_id
32GROUP BY 1, 2, 3, 4, 5, 6, 7