CREATE MATERIALIZED VIEW insights.cash_values_journal_density_mv AS
WITH series AS (
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
settled_cash_balance,
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_cash_series_mv AS settled_cash_series_mv_next
), 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 close IS NULL
), series_binned AS (
SELECT
s.account_id,
s.asset_id,
s.currency_code,
s.dim_settlement_date,
s.settled_cash_balance,
s.next_settlement_date,
spine.dim_value_month
FROM series 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_cash_balance * px.close AS market_value,
px.close AS average_cost_per_unit,
s.settled_cash_balance AS purchased_quantity
FROM series_binned AS s
JOIN (
SELECT
asset_id,
date,
close,
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 close 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
)