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

CREATE MATERIALIZED VIEW insights.intraday_fx_rates_latest_mv AS
WITH intraday AS (
  SELECT
    source_currency_code,
    target_currency_code,
    ARG_MAX(rate, window_start) AS rate,
    MAX(value_timestamp) AS value_timestamp
  FROM insights.intraday_fx_rates_5min_mv
  GROUP BY
    source_currency_code,
    target_currency_code
), pair_keys AS (
  SELECT
    source_currency_code,
    target_currency_code
  FROM (
    SELECT
      source_currency_code,
      target_currency_code
    FROM intraday
    UNION ALL
    SELECT
      source_currency_code,
      target_currency_code
    FROM insights.fx_rates_snapshot_mv AS fx_rates_snapshot_mv_next
  )
  GROUP BY
    source_currency_code,
    target_currency_code
)
SELECT
  k.source_currency_code,
  k.target_currency_code,
  COALESCE(i.rate, e.rate) AS rate,
  CASE WHEN NOT i.rate IS NULL THEN i.value_timestamp ELSE e.value_timestamp END AS value_timestamp
FROM pair_keys AS k
LEFT JOIN intraday AS i
  ON i.source_currency_code = k.source_currency_code
  AND i.target_currency_code = k.target_currency_code
LEFT JOIN insights.fx_rates_snapshot_mv AS e
  ON e.source_currency_code = k.source_currency_code
  AND e.target_currency_code = k.target_currency_code