CREATE MATERIALIZED VIEW adib_rm.sdk_activity_transactions_merged_mv AS
WITH eod_bounded AS (
SELECT
t.transaction_id,
t.account_id,
t.asset_id,
COALESCE(it.source_asset_id, ft.source_asset_id) AS source_asset_id,
t.transaction_valuation_date,
t.transaction_valuation_timestamp,
t.transaction_type_id,
t.currency_code,
t.gross_value,
t.net_value,
t.quantity,
t.order_id,
tt.order_side_label_id
FROM olap.transactions_dm AS t
LEFT JOIN olap.trade_transactions_dm AS tt
ON tt.transaction_id = t.transaction_id
LEFT JOIN olap.income_transactions_dm AS it
ON it.transaction_id = t.transaction_id
LEFT JOIN olap.fee_transactions_dm AS ft
ON ft.transaction_id = t.transaction_id
WHERE
t.disabled_at IS NULL
AND t.transaction_valuation_date >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
), eod_ids AS (
SELECT
transaction_id
FROM olap.transactions_dm AS transactions_dm_next
), eod_twin_keys AS (
SELECT DISTINCT
order_id,
account_id,
asset_id,
transaction_type_id
FROM olap.transactions_dm AS transactions_dm_next
WHERE
order_id <> ''
), live_eod_twin_keys AS (
SELECT
order_id,
account_id,
asset_id,
transaction_type_id,
MAX(transaction_valuation_timestamp) AS latest_twin_valuation_timestamp
FROM eod_bounded
WHERE
order_id <> ''
GROUP BY
order_id,
account_id,
asset_id,
transaction_type_id
), intraday_kept AS (
SELECT
transaction_id,
account_id,
asset_id,
CAST(NULL AS VARCHAR) AS source_asset_id,
transaction_valuation_date,
transaction_valuation_timestamp,
transaction_type_id,
currency_code,
gross_value,
net_value,
quantity,
order_id,
order_side_label_id
FROM olap.transactions_intraday_dm
WHERE
disabled_at IS NULL
AND status_label_id IN (
SELECT
status_label_id
FROM adib_rm.active_transaction_status_id_mv
)
AND transaction_valuation_date >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
), intraday_settled AS (
SELECT
transaction_id,
account_id,
asset_id,
CAST(NULL AS VARCHAR) AS source_asset_id,
transaction_valuation_date,
transaction_valuation_timestamp,
transaction_type_id,
currency_code,
gross_value,
net_value,
quantity,
order_id,
order_side_label_id
FROM olap.transactions_intraday_dm
WHERE
retire_reason = 'SETTLED'
AND transaction_valuation_date >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
)
SELECT
ik.transaction_id,
ik.account_id,
ik.asset_id,
ik.source_asset_id,
ik.transaction_valuation_date,
ik.transaction_valuation_timestamp,
ik.transaction_type_id,
ik.currency_code,
ik.gross_value,
ik.net_value,
ik.quantity,
ik.order_id,
ik.order_side_label_id,
CAST('INTRADAY' AS VARCHAR) AS transaction_source
FROM intraday_kept AS ik
LEFT JOIN live_eod_twin_keys AS ltk
ON ik.order_id <> ''
AND ltk.order_id = ik.order_id
AND ltk.account_id = ik.account_id
AND ltk.asset_id = ik.asset_id
AND ltk.transaction_type_id = ik.transaction_type_id
AND ltk.latest_twin_valuation_timestamp >= ik.transaction_valuation_timestamp
WHERE
ltk.order_id IS NULL
UNION ALL
SELECT
eod.transaction_id,
eod.account_id,
eod.asset_id,
eod.source_asset_id,
eod.transaction_valuation_date,
eod.transaction_valuation_timestamp,
eod.transaction_type_id,
eod.currency_code,
eod.gross_value,
eod.net_value,
eod.quantity,
eod.order_id,
eod.order_side_label_id,
CAST('EOD' AS VARCHAR) AS transaction_source
FROM eod_bounded AS eod
LEFT JOIN intraday_kept AS ik
ON ik.transaction_id = eod.transaction_id
WHERE
ik.transaction_id IS NULL
UNION ALL
SELECT
s.transaction_id,
s.account_id,
s.asset_id,
s.source_asset_id,
s.transaction_valuation_date,
s.transaction_valuation_timestamp,
s.transaction_type_id,
s.currency_code,
s.gross_value,
s.net_value,
s.quantity,
s.order_id,
s.order_side_label_id,
CAST('INTRADAY_SETTLING' AS VARCHAR) AS transaction_source
FROM intraday_settled AS s
LEFT JOIN intraday_kept AS ik
ON ik.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
ik.transaction_id IS NULL
AND eod_ids.transaction_id IS NULL
AND tk.order_id IS NULL