CREATE MATERIALIZED VIEW insights.settled_cost_basis_series_mv AS
WITH changes AS (
SELECT
account_id,
asset_id,
type,
currency_code,
dim_value_date AS effective_from,
average_cost_per_unit,
average_cost_per_unit_system_currency,
total_cost_system_currency,
cost_fx_provenance,
purchased_quantity,
m_is_stub
FROM (
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,
LAG(average_cost_per_unit) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_value_date) AS prev_cost,
LAG(average_cost_per_unit_system_currency) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_value_date) AS prev_cost_system_currency,
LAG(total_cost_system_currency) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_value_date) AS prev_total_cost_system_currency,
LAG(cost_fx_provenance) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_value_date) AS prev_cost_fx_provenance
FROM insights.settled_cost_basis_carried_mv
) AS c
WHERE
(
prev_cost IS DISTINCT FROM average_cost_per_unit
)
OR (
prev_cost_system_currency IS DISTINCT FROM average_cost_per_unit_system_currency
)
OR (
prev_total_cost_system_currency IS DISTINCT FROM total_cost_system_currency
)
OR (
prev_cost_fx_provenance IS DISTINCT FROM cost_fx_provenance
)
)
SELECT
account_id,
asset_id,
type,
currency_code,
effective_from,
LEAD(effective_from) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY effective_from) AS effective_to,
average_cost_per_unit,
average_cost_per_unit_system_currency,
total_cost_system_currency,
cost_fx_provenance,
purchased_quantity,
m_is_stub
FROM changes