CREATE MATERIALIZED VIEW insights.holding_values_journal_density_mv AS
WITH series_windows AS (
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
settled_quantity,
LEAD(dim_settlement_date) OVER (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_settlement_date) AS next_settlement_date
FROM insights.settled_position_series_mv
), price_month_spine AS (
SELECT DISTINCT
asset_id,
CAST(DATE_TRUNC('MONTH', date) AS DATE) AS dim_value_month
FROM asset_service.asset_prices_eod_ft AS asset_prices_eod_ft_next
WHERE
NOT reference_price IS NULL
), series_binned AS (
SELECT
s.account_id,
s.asset_id,
s.currency_code,
s.dim_settlement_date,
s.settled_quantity,
s.next_settlement_date,
spine.dim_value_month
FROM series_windows AS s
JOIN price_month_spine AS spine
ON spine.asset_id = s.asset_id
AND spine.dim_value_month >= CAST(DATE_TRUNC('MONTH', s.dim_settlement_date) AS DATE)
AND (
s.next_settlement_date IS NULL OR spine.dim_value_month <= s.next_settlement_date
)
)
SELECT
s.account_id,
s.asset_id,
px.date AS dim_value_date,
CAST('ASSET' AS VARCHAR) AS type,
s.currency_code,
s.settled_quantity * px.reference_price AS market_value,
cb.average_cost_per_unit,
cb.average_cost_per_unit_system_currency,
cb.total_cost_system_currency,
cb.cost_fx_provenance,
s.settled_quantity AS purchased_quantity
FROM series_binned AS s
JOIN (
SELECT
asset_id,
date,
reference_price,
CAST(DATE_TRUNC('MONTH', date) AS DATE) AS dim_value_month
FROM asset_service.asset_prices_eod_ft AS asset_prices_eod_ft_next
WHERE
NOT reference_price IS NULL
) AS px
ON px.asset_id = s.asset_id
AND px.dim_value_month = s.dim_value_month
AND px.date >= s.dim_settlement_date
AND (
s.next_settlement_date IS NULL OR px.date < s.next_settlement_date
)
LEFT JOIN insights.settled_cost_basis_series_mv AS cb
ON cb.account_id = s.account_id
AND cb.asset_id = s.asset_id
AND cb.currency_code = s.currency_code
AND cb.effective_from <= px.date
AND (
cb.effective_to IS NULL OR px.date < cb.effective_to
)