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

CREATE MATERIALIZED VIEW insights.position_by_asset_mv AS
WITH agg AS (
  SELECT
    hv.account_group_id,
    hv.source_entity_type,
    hv.dim_value_date AS dim_balance_date,
    COALESCE(hv.position_type, 'POSITION') AS position_type,
    hv.asset_id,
    hv.asset_currency,
    hv.holding_currency,
    hv.group_currency AS currency_code,
    SUM(hv.market_value_group) AS market_value,
    SUM(hv.average_cost_group) AS total_average_cost,
    SUM(hv.fair_value_group) AS fair_value,
    SUM(hv.accrued_value_group) AS accrued_value,
    SUM(hv.market_value_system) AS market_value_system_currency,
    SUM(hv.average_cost_system) AS total_average_cost_system_currency,
    SUM(hv.fair_value_system) AS fair_value_system_currency,
    SUM(hv.accrued_value_system) AS accrued_value_system_currency,
    SUM(hv.market_value_holding) AS market_value_holding,
    SUM(hv.average_cost_holding) AS total_average_cost_holding,
    SUM(hv.fair_value_holding) AS fair_value_holding,
    SUM(hv.purchased_quantity) AS quantity
  FROM insights.position_values_mv AS hv
  GROUP BY
    GROUPING SETS (
      (
        hv.account_group_id,
        hv.source_entity_type,
        hv.dim_value_date,
        hv.position_type,
        hv.asset_id,
        hv.asset_currency,
        hv.holding_currency,
        hv.group_currency
      ),
      (
        hv.account_group_id,
        hv.source_entity_type,
        hv.dim_value_date,
        hv.asset_id,
        hv.asset_currency,
        hv.holding_currency,
        hv.group_currency
      )
    )
)
SELECT
  p.account_group_id,
  p.source_entity_type,
  p.dim_balance_date,
  p.position_type,
  p.asset_id,
  p.asset_currency,
  p.currency_code,
  p.market_value,
  p.total_average_cost,
  p.fair_value,
  p.accrued_value,
  p.market_value_system_currency,
  p.total_average_cost_system_currency,
  p.fair_value_system_currency,
  p.accrued_value_system_currency,
  p.market_value_holding * COALESCE(
    fx_asset.rate,
    CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
  ) AS market_value_asset_currency,
  p.total_average_cost_holding * COALESCE(
    fx_asset.rate,
    CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
  ) AS total_average_cost_asset_currency,
  p.fair_value_holding * COALESCE(
    fx_asset.rate,
    CASE WHEN p.holding_currency = p.asset_currency THEN 1 ELSE NULL END
  ) AS fair_value_asset_currency,
  p.quantity,
  p.market_value_system_currency / NULLIF(b.market_value_system_currency, 0) AS weight,
  p.fair_value_system_currency / NULLIF(b.fair_value_system_currency, 0) AS fair_value_weight
FROM agg AS p
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_asset
  ON fx_asset.source_currency_code = p.holding_currency
  AND fx_asset.target_currency_code = p.asset_currency
  AND fx_asset.date = p.dim_balance_date
JOIN insights.position_summary_mv AS b
  ON b.account_group_id = p.account_group_id
  AND b.dim_balance_date = p.dim_balance_date
  AND b.position_type = p.position_type
  AND b.currency_code = p.currency_code
WHERE
  NOT fx_asset.rate IS NULL OR p.holding_currency = p.asset_currency