RWM Console cluster: risingwave-ai-hub-int.ai-hub-rwm.svc.cluster.local

CREATE MATERIALIZED VIEW insights.flow_by_asset_mv AS
SELECT
  account_group_id,
  dim_transaction_date,
  asset_id,
  asset_currency,
  position_type,
  currency_code,
  adjusted_net_flow,
  adjusted_net_flow_system_currency,
  adjusted_net_flow_asset_currency,
  SUM(adjusted_net_flow) OVER w AS cumulative_adjusted_netflow,
  SUM(adjusted_net_flow_system_currency) OVER w AS cumulative_adjusted_netflow_system_currency,
  SUM(adjusted_net_flow_asset_currency) OVER w AS cumulative_adjusted_netflow_asset_currency
FROM (
  SELECT
    pre.account_group_id,
    pre.dim_transaction_date,
    pre.asset_id,
    pre.asset_currency,
    CASE WHEN GROUPING(pre.position_type) = 1 THEN 'POSITION' ELSE pre.position_type END AS position_type,
    pre.group_currency AS currency_code,
    SUM(pre.net_value_group) AS adjusted_net_flow,
    SUM(pre.net_value_system) AS adjusted_net_flow_system_currency,
    SUM(
      pre.net_value * COALESCE(
        fx_asset.rate,
        CASE WHEN pre.transaction_currency = pre.asset_currency THEN 1 ELSE NULL END
      )
    ) AS adjusted_net_flow_asset_currency
  FROM (
    SELECT
      account_group_id,
      dim_transaction_date,
      asset_id,
      asset_currency,
      transaction_currency,
      position_type,
      group_currency,
      SUM(net_value_group) AS net_value_group,
      SUM(net_value_system) AS net_value_system,
      SUM(net_value) AS net_value
    FROM insights.flow_values_mv AS flow_values_mv_next
    GROUP BY
      account_group_id,
      dim_transaction_date,
      asset_id,
      asset_currency,
      transaction_currency,
      position_type,
      group_currency
  ) AS pre
  LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_asset
    ON fx_asset.source_currency_code = pre.transaction_currency
    AND fx_asset.target_currency_code = pre.asset_currency
    AND fx_asset.date = pre.dim_transaction_date
  WHERE
    NOT fx_asset.rate IS NULL OR pre.transaction_currency = pre.asset_currency
  GROUP BY
    GROUPING SETS (
      (
        pre.account_group_id,
        pre.dim_transaction_date,
        pre.asset_id,
        pre.asset_currency,
        pre.position_type,
        pre.group_currency
      ),
      (
        pre.account_group_id,
        pre.dim_transaction_date,
        pre.asset_id,
        pre.asset_currency,
        pre.group_currency
      )
    )
  HAVING
    GROUPING(pre.position_type) = 1 OR NOT pre.position_type IS NULL
) AS grouped
WINDOW w AS (
  PARTITION BY account_group_id, asset_id, position_type
  ORDER BY dim_transaction_date
)