CREATE MATERIALIZED VIEW insights.transactions_merged_mv AS
WITH eod AS (
SELECT
transaction_id,
account_id,
asset_id,
transaction_valuation_date,
transaction_valuation_timestamp,
transaction_settlement_date,
transaction_type_id,
currency_code,
net_value,
quantity
FROM olap.transactions_dm
WHERE
disabled_at IS NULL
), intraday_visible AS (
SELECT
i.transaction_id,
i.account_id,
i.asset_id,
i.transaction_valuation_date,
i.transaction_valuation_timestamp,
i.transaction_settlement_date,
i.transaction_type_id,
i.currency_code,
i.net_value,
i.quantity,
CAST('INTRADAY' AS VARCHAR) AS transaction_source
FROM olap.transactions_intraday_dm AS i
WHERE
i.disabled_at IS NULL
AND i.status_label_id IN (
SELECT
status_label_id
FROM insights.active_transaction_status_id_mv
)
), eod_ids AS (
SELECT
transaction_id
FROM olap.transactions_dm
), eod_twin_keys AS (
SELECT DISTINCT
order_id,
account_id,
asset_id,
transaction_type_id
FROM olap.transactions_dm
WHERE
order_id <> ''
), intraday_settled AS (
SELECT
i.transaction_id,
i.account_id,
i.asset_id,
i.transaction_valuation_date,
i.transaction_valuation_timestamp,
i.transaction_settlement_date,
i.transaction_type_id,
i.currency_code,
i.net_value,
i.quantity,
i.order_id,
CAST('INTRADAY_SETTLING' AS VARCHAR) AS transaction_source
FROM olap.transactions_intraday_dm AS i
WHERE
i.retire_reason = 'SETTLED'
)
SELECT
transaction_id,
account_id,
asset_id,
transaction_valuation_date,
transaction_valuation_timestamp,
transaction_settlement_date,
transaction_type_id,
currency_code,
net_value,
quantity,
transaction_source
FROM intraday_visible
UNION ALL
SELECT
eod.transaction_id,
eod.account_id,
eod.asset_id,
eod.transaction_valuation_date,
eod.transaction_valuation_timestamp,
eod.transaction_settlement_date,
eod.transaction_type_id,
eod.currency_code,
eod.net_value,
eod.quantity,
CAST('EOD' AS VARCHAR) AS transaction_source
FROM eod
LEFT JOIN intraday_visible AS i
ON i.transaction_id = eod.transaction_id
WHERE
i.transaction_id IS NULL
UNION ALL
SELECT
s.transaction_id,
s.account_id,
s.asset_id,
s.transaction_valuation_date,
s.transaction_valuation_timestamp,
s.transaction_settlement_date,
s.transaction_type_id,
s.currency_code,
s.net_value,
s.quantity,
s.transaction_source
FROM intraday_settled AS s
LEFT JOIN intraday_visible AS v
ON v.transaction_id = s.transaction_id
LEFT JOIN eod_ids
ON eod_ids.transaction_id = s.transaction_id
LEFT JOIN eod_twin_keys AS tk
ON s.order_id <> ''
AND tk.order_id = s.order_id
AND tk.account_id = s.account_id
AND tk.asset_id = s.asset_id
AND tk.transaction_type_id = s.transaction_type_id
WHERE
v.transaction_id IS NULL
AND eod_ids.transaction_id IS NULL
AND tk.order_id IS NULL