CREATE MATERIALIZED VIEW alpheya_agent.transactions_mv
WITH (
backfill_order=FIXED(olap.transaction_types_dm -> olap.transactions_dm)
) AS
SELECT
t.transaction_id,
t.account_id,
t.portfolio_id,
t.asset_id,
COALESCE(tt.type, 'OTHER') AS transaction_type,
t.transaction_type_id,
t.currency_code,
t.quantity,
t.unit_price,
t.gross_value,
t.net_value,
t.valuation_date,
t.settlement_date,
t.order_id,
t.external_reference,
tt.name_en AS description
FROM (
SELECT
tx.transaction_id,
tx.account_id,
atp.portfolio_id,
tx.asset_id,
tx.transaction_type_id,
tx.currency_code,
tx.quantity,
tx.unit_price,
tx.gross_value,
tx.net_value,
tx.transaction_valuation_date AS valuation_date,
tx.transaction_settlement_date AS settlement_date,
tx.order_id,
tx.external_reference,
ROW_NUMBER() OVER (
PARTITION BY tx.transaction_id
ORDER BY atp.effective_start_date DESC NULLS LAST, atp.portfolio_id DESC
) AS link_rank
FROM olap.transactions_dm AS tx
LEFT JOIN olap.account_to_portfolios_dm AS atp
ON atp.account_id = tx.account_id
AND atp.disabled_at IS NULL
AND atp.effective_start_date <= tx.transaction_valuation_date
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > tx.transaction_valuation_date
)
WHERE
tx.disabled_at IS NULL
) AS t
LEFT JOIN olap.transaction_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS tt
ON tt.transaction_type_id = t.transaction_type_id
WHERE
t.link_rank = 1