CREATE MATERIALIZED VIEW adib_rm.activity_transaction_portfolios_mv AS
SELECT
t.transaction_id,
CASE
WHEN o.portfolio_id IS NULL
THEN CAST(ARRAY[] AS VARCHAR[])
ELSE CAST(ARRAY[o.portfolio_id] AS VARCHAR[])
END AS portfolio_ids
FROM adib_rm.sdk_activity_transactions_merged_mv AS t
LEFT JOIN order_service.orders AS o
ON o.id = t.order_id
WHERE
NOT t.order_id IS NULL
UNION ALL
SELECT
l.transaction_id,
CAST(ARRAY_AGG(DISTINCT l.portfolio_id) AS VARCHAR[]) AS portfolio_ids
FROM adib_rm.activity_transaction_account_portfolio_links_mv AS l
GROUP BY
l.transaction_id