CREATE MATERIALIZED VIEW insights.settled_cost_basis_carried_mv AS
WITH real_groups AS (
SELECT
account_id,
asset_id,
type,
currency_code,
dim_value_date,
average_cost_per_unit,
average_cost_per_unit_system_currency,
total_cost_system_currency,
cost_fx_provenance,
purchased_quantity,
m_is_stub,
SUM(CASE WHEN NOT m_is_stub IS TRUE THEN 1 ELSE 0 END) OVER (
PARTITION BY account_id, asset_id, currency_code
ORDER BY dim_value_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS real_group
FROM insights.settled_cost_basis_dedup_mv
)
SELECT
account_id,
asset_id,
type,
currency_code,
dim_value_date,
average_cost_per_unit,
CASE
WHEN m_is_stub IS TRUE
THEN MAX(
CASE
WHEN NOT m_is_stub IS TRUE
THEN average_cost_per_unit_system_currency
ELSE NULL
END
) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
ELSE average_cost_per_unit_system_currency
END AS average_cost_per_unit_system_currency,
CASE
WHEN m_is_stub IS TRUE
THEN MAX(CASE WHEN NOT m_is_stub IS TRUE THEN total_cost_system_currency ELSE NULL END) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
ELSE total_cost_system_currency
END AS total_cost_system_currency,
CASE
WHEN m_is_stub IS TRUE
THEN MAX(CASE WHEN NOT m_is_stub IS TRUE THEN cost_fx_provenance ELSE NULL END) OVER (PARTITION BY account_id, asset_id, currency_code, real_group)
ELSE cost_fx_provenance
END AS cost_fx_provenance,
purchased_quantity,
m_is_stub
FROM real_groups