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

CREATE MATERIALIZED VIEW insights.position_values_mv
WITH (
  backfill_order=FIXED(asset_service.assets_dm_next -> insights.holding_values_mv_next, asset_service.foreign_exchange_rates_eod_ft -> insights.holding_values_mv_next)
) AS
SELECT
  h.account_id,
  h.asset_id,
  atag.account_group_id,
  atag.source_entity_type,
  h.dim_value_date,
  h.type AS position_type,
  h.currency_code AS holding_currency,
  atag.base_currency AS group_currency,
  h.asset_currency,
  h.asset_type,
  h.market_value AS market_value_holding,
  h.average_cost AS average_cost_holding,
  h.purchased_quantity,
  h.fair_value AS fair_value_holding,
  h.accrued_value,
  COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fx_rate_to_group,
  h.market_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS market_value_group,
  h.average_cost * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS average_cost_group,
  h.fair_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_value_group,
  h.accrued_value * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS accrued_value_group,
  ABS(h.market_value) * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS relationship_value_group,
  ABS(h.fair_value) * COALESCE(fx_group.rate, CASE WHEN h.currency_code = atag.base_currency THEN 1 ELSE NULL END) AS fair_relationship_value_group,
  h.market_value_system_currency AS market_value_system,
  h.average_cost_system_currency AS average_cost_system,
  h.fair_value_system_currency AS fair_value_system,
  h.accrued_value_system_currency AS accrued_value_system,
  ABS(h.market_value_system_currency) AS relationship_value_system,
  ABS(h.fair_value_system_currency) AS fair_relationship_value_system
FROM (
  SELECT
    hv.account_id,
    hv.asset_id,
    hv.dim_value_date,
    hv.type,
    hv.currency_code,
    hv.market_value,
    hv.average_cost,
    hv.purchased_quantity,
    hv.fair_value,
    hv.accrued_value,
    hv.market_value_system_currency,
    hv.average_cost_system_currency,
    hv.fair_value_system_currency,
    hv.accrued_value_system_currency,
    a.issue_currency_code AS asset_currency,
    COALESCE(a.type, 'UNSPECIFIED') AS asset_type,
    CAST(DATE_TRUNC('MONTH', hv.dim_value_date) AS DATE) AS dim_value_month
  FROM insights.holding_values_mv AS hv
  LEFT JOIN asset_service.assets_dm AS a
    ON a.id = hv.asset_id
) AS h
JOIN insights.account_to_account_groups_binned_mv AS atag
  ON atag.account_id = h.account_id
  AND atag.dim_value_month = h.dim_value_month
  AND h.dim_value_date >= atag.effective_start_date
  AND (
    atag.effective_end_date IS NULL OR h.dim_value_date < atag.effective_end_date
  )
  AND (
    atag.opening_date IS NULL OR h.dim_value_date >= atag.opening_date
  )
  AND atag.source_entity_type <> 'user'
LEFT JOIN asset_service.foreign_exchange_rates_eod_ft AS fx_group
  ON fx_group.source_currency_code = h.currency_code
  AND fx_group.target_currency_code = atag.base_currency
  AND fx_group.date = h.dim_value_date
WHERE
  NOT fx_group.rate IS NULL OR h.currency_code = atag.base_currency