Opportunity pipeline

1-- core.salesforce.opportunity_pipeline
2-- Every opportunity with its outcome, stage, account and owner.
3-- Grain: one row per opportunity (salesforce_org x opportunity_id).
4-- Depends on: staging.salesforce.opportunity, .opportunity_stage, .account, .user
5--
6-- OUTCOME comes from Salesforce, not from stage names. is_closed and is_won are
7-- set by the stage's Type and Probability settings in Setup, so custom stages are
8-- classified the way Salesforce classifies them and there is nothing to maintain.
9--
10-- CURRENCY. In a single-currency org currency_code is ORG_DEFAULT and every amount
11-- is in the org currency. In a multi-currency org (see staging.salesforce.opportunity)
12-- amounts are in each opportunity's own currency: group by currency_code before
13-- summing. Converting to a corporate currency needs Salesforce's CurrencyType or
14-- DatedConversionRate objects and is not done here.
15--
16-- CLOSE DATE. For an open opportunity close_date is the expected close date. For a
17-- closed one it is whatever the rep left in the field: Salesforce does not set it
18-- automatically on close. core.salesforce.opportunity_stage_history has the
19-- moment the stage actually changed.
20--
21-- Current state only. For the pipeline as it stood on an earlier date, use the
22-- stage history model or materialise this one on a schedule and keep the runs.
23
24WITH stages AS (
25    -- One row per stage name, so a stage can never fan an opportunity out.
26    SELECT *
27    FROM {{staging.salesforce.opportunity_stage}}
28    QUALIFY ROW_NUMBER() OVER (
29        PARTITION BY salesforce_org, stage_name ORDER BY stage_is_active DESC, stage_id
30    ) = 1
31)
32
33SELECT
34    o.salesforce_org,
35    o.opportunity_id,
36    o.opportunity_name,
37    o.opportunity_type,
38    o.lead_source,
39
40    o.stage_name,