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

CREATE MATERIALIZED VIEW alinma_bff.party_advisor_kpi_period_values_mv AS
WITH portfolio_facts AS (
  SELECT
    portfolio_id,
    SUM(trv_growth_weekly) AS trv_growth_weekly,
    SUM(trv_growth_monthly) AS trv_growth_monthly,
    SUM(trv_growth_quarterly) AS trv_growth_quarterly,
    SUM(trv_growth_yearly) AS trv_growth_yearly,
    SUM(net_assets_growth_weekly) AS net_assets_growth_weekly,
    SUM(net_assets_growth_monthly) AS net_assets_growth_monthly,
    SUM(net_assets_growth_quarterly) AS net_assets_growth_quarterly,
    SUM(net_assets_growth_yearly) AS net_assets_growth_yearly,
    SUM(fee_weekly) AS fee_weekly,
    SUM(fee_monthly) AS fee_monthly,
    SUM(fee_quarterly) AS fee_quarterly,
    SUM(fee_yearly) AS fee_yearly,
    SUM(gain_loss_weekly) AS gain_loss_weekly,
    SUM(gain_loss_monthly) AS gain_loss_monthly,
    SUM(gain_loss_quarterly) AS gain_loss_quarterly,
    SUM(gain_loss_yearly) AS gain_loss_yearly
  FROM (
    SELECT
      portfolio_id,
      trv_growth_weekly,
      trv_growth_monthly,
      trv_growth_quarterly,
      trv_growth_yearly,
      net_assets_growth_weekly,
      net_assets_growth_monthly,
      net_assets_growth_quarterly,
      net_assets_growth_yearly,
      CAST(NULL AS DECIMAL) AS fee_weekly,
      CAST(NULL AS DECIMAL) AS fee_monthly,
      CAST(NULL AS DECIMAL) AS fee_quarterly,
      CAST(NULL AS DECIMAL) AS fee_yearly,
      CAST(NULL AS DECIMAL) AS gain_loss_weekly,
      CAST(NULL AS DECIMAL) AS gain_loss_monthly,
      CAST(NULL AS DECIMAL) AS gain_loss_quarterly,
      CAST(NULL AS DECIMAL) AS gain_loss_yearly
    FROM alinma_bff.advisor_kpi_portfolio_growth_mv AS advisor_kpi_portfolio_growth_mv_next
    UNION ALL
    SELECT
      portfolio_id,
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      fee_weekly,
      fee_monthly,
      fee_quarterly,
      fee_yearly,
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL)
    FROM alinma_bff.advisor_kpi_portfolio_fee_mv AS advisor_kpi_portfolio_fee_mv_next
    UNION ALL
    SELECT
      portfolio_id,
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      CAST(NULL AS DECIMAL),
      gain_loss_weekly,
      gain_loss_monthly,
      gain_loss_quarterly,
      gain_loss_yearly
    FROM alinma_bff.advisor_kpi_portfolio_pnl_growth_mv AS advisor_kpi_portfolio_pnl_growth_mv_next
  ) AS facts
  GROUP BY
    portfolio_id
)
SELECT
  up.user_id,
  SUM(pf.trv_growth_weekly) AS trv_growth_weekly,
  SUM(pf.trv_growth_monthly) AS trv_growth_monthly,
  SUM(pf.trv_growth_quarterly) AS trv_growth_quarterly,
  SUM(pf.trv_growth_yearly) AS trv_growth_yearly,
  SUM(pf.net_assets_growth_weekly) AS net_assets_growth_weekly,
  SUM(pf.net_assets_growth_monthly) AS net_assets_growth_monthly,
  SUM(pf.net_assets_growth_quarterly) AS net_assets_growth_quarterly,
  SUM(pf.net_assets_growth_yearly) AS net_assets_growth_yearly,
  SUM(pf.fee_weekly) AS fee_weekly,
  SUM(pf.fee_monthly) AS fee_monthly,
  SUM(pf.fee_quarterly) AS fee_quarterly,
  SUM(pf.fee_yearly) AS fee_yearly,
  SUM(pf.gain_loss_weekly) AS gain_loss_weekly,
  SUM(pf.gain_loss_monthly) AS gain_loss_monthly,
  SUM(pf.gain_loss_quarterly) AS gain_loss_quarterly,
  SUM(pf.gain_loss_yearly) AS gain_loss_yearly
FROM alinma_bff.party_advisor_kpi_user_portfolios_mv AS up
LEFT JOIN portfolio_facts AS pf
  ON pf.portfolio_id = up.portfolio_id
GROUP BY
  up.user_id