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

CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_account_value_daily_mv AS
SELECT
  h.account_id,
  h.dim_value_date,
  SUM(CASE WHEN c.product_class = 'AUM' THEN h.market_value_system_currency ELSE 0 END) AS aum,
  SUM(
    CASE
      WHEN c.product_class = 'DEPOSITS'
      THEN h.market_value_system_currency
      ELSE 0
    END
  ) AS deposits,
  SUM(
    CASE
      WHEN c.product_class = 'FINANCING'
      THEN ABS(h.market_value_system_currency)
      ELSE 0
    END
  ) AS financing
FROM insights.holding_values_mv AS h
JOIN alinma_bff.advisor_kpi_account_product_class_mv AS c
  ON c.account_id = h.account_id
WHERE
  NOT h.market_value_system_currency IS NULL
  AND CAST(h.dim_value_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '1900 DAYS'
  AND (
    h.dim_value_date - CAST(DATE_TRUNC('MONTH', h.dim_value_date) AS DATE) >= 14
    OR CAST(h.dim_value_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '400 DAYS'
  )
GROUP BY
  h.account_id,
  h.dim_value_date