RWM Console cluster: risingwave-adib.adib-rw.svc.cluster.local

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,
    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
  WHERE
    transaction_valuation_date >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
), intraday_kept AS (
  SELECT
    transaction_id,
    account_id,
    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 = ID_FROM_STRING_WITH_PREFIX('label_transaction_status', 'ACTIVE')
    AND transaction_valuation_date >= CURRENT_TIMESTAMP - INTERVAL '2 YEARS'
)
SELECT
  transaction_id,
  account_id,
  asset_id,
  transaction_valuation_date,
  transaction_valuation_timestamp,
  transaction_type_id,
  currency_code,
  gross_value,
  net_value,
  quantity,
  order_id,
  order_side_label_id,
  CAST('INTRADAY' AS VARCHAR) AS transaction_source
FROM intraday_kept
UNION ALL
SELECT
  eod.transaction_id,
  eod.account_id,
  eod.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