CREATE MATERIALIZED VIEW insights.settled_position_series_mv
WITH (
backfill_order=FIXED(asset_service.assets_dm -> insights.transactions_merged_mv)
) AS
WITH security_legs AS (
SELECT
t.account_id,
t.asset_id,
t.currency_code,
t.transaction_settlement_date AS dim_settlement_date,
t.transaction_id,
COALESCE(t.quantity, CAST(0 AS DECIMAL)) AS settlement_quantity_delta,
t.net_value AS settlement_value_delta
FROM insights.transactions_merged_mv AS t
LEFT JOIN asset_service.assets_dm FOR SYSTEM_TIME AS OF PROCTIME() AS a
ON a.id = t.asset_id
WHERE
NOT t.transaction_settlement_date IS NULL
AND COALESCE(a.type, 'UNSPECIFIED') <> 'CASH'
), daily AS (
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
SUM(settlement_quantity_delta) AS settlement_quantity_delta,
SUM(settlement_value_delta) AS settlement_value_delta,
COUNT(transaction_id) AS transaction_count
FROM security_legs
GROUP BY
account_id,
asset_id,
currency_code,
dim_settlement_date
)
SELECT
account_id,
asset_id,
currency_code,
dim_settlement_date,
settlement_quantity_delta,
settlement_value_delta,
SUM(settlement_quantity_delta) OVER w AS settled_quantity,
SUM(settlement_value_delta) OVER w AS settled_value,
transaction_count
FROM daily
WINDOW w AS (PARTITION BY account_id, asset_id, currency_code ORDER BY dim_settlement_date)