sdk_portfolio_chart_mv · adib_rm

materialized view · adib_rm.sdk_portfolio_chart_mv
Parallelism
2
Actors
45 / 45
running
Distribution
HASH
Rows
0
State size
—
Created
2026-06-30 21:59
Initialized
2026-06-30 21:59
Fragment flags
MVIEWNOWSTREAM_SCAN
Actors
Actor	Fragment	Worker	State
53547	3138	3	running
53548	3138	3	running
53551	3140	3	running
53552	3140	3	running
55175	3133	3	running
55176	3133	3	running
55221	3134	3	running
55222	3134	3	running
55223	3136	3	running
55224	3136	3	running
55225	3145	3	running
55226	3145	3	running
+ 33 more actor(s) (45 running)
sql · adib_rm.sdk_portfolio_chart_mv — click to expand
CREATE MATERIALIZED VIEW adib_rm.sdk_portfolio_chart_mv AS
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_1W' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
  AND pvh.dim_balance_date >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS'
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
UNION ALL
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_1M' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
  AND pvh.dim_balance_date >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS'
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
UNION ALL
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_3M' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
  AND pvh.dim_balance_date >= CURRENT_TIMESTAMP - INTERVAL '90 DAYS'
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
UNION ALL
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_YTD' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
  AND pvh.dim_balance_date >= DATE_TRUNC('YEAR', CURRENT_TIMESTAMP)
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
UNION ALL
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_1Y' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
  AND pvh.dim_balance_date >= CURRENT_TIMESTAMP - INTERVAL '365 DAYS'
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
UNION ALL
SELECT
  pag.portfolio_id,
  CAST('HISTORIC_RANGE_ALL' AS VARCHAR) AS historic_range,
  JSONB_AGG(
    JSONB_BUILD_OBJECT(
      'date',
      CAST(pvh.dim_balance_date AS VARCHAR),
      'marketValue',
      JSONB_BUILD_OBJECT('amount', CAST(pvh.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
      'unrealizedGainLoss',
      JSONB_BUILD_OBJECT(
        'value',
        JSONB_BUILD_OBJECT(
          'amount',
          CAST(pnl.unrealized_gain_loss AS VARCHAR),
          'currencyCode',
          p.base_currency_code
        ),
        'percentage',
        CASE
          WHEN pnl.total_average_cost IS NULL OR pnl.total_average_cost = 0
          THEN NULL
          ELSE CAST((
            pnl.unrealized_gain_loss / pnl.total_average_cost * 100
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm FOR SYSTEM_TIME AS OF PROCTIME() AS p
  ON p.portfolio_id = pag.portfolio_id AND p.closing_date IS NULL
JOIN insights.position_summary_mv AS pvh
  ON pvh.account_group_id = pag.account_group_id
  AND pvh.position_type = 'POSITION'
  AND pvh.currency_code = p.base_currency_code
LEFT JOIN insights.pnl_summary_mv AS pnl
  ON pnl.account_group_id = pvh.account_group_id
  AND pnl.position_type = pvh.position_type
  AND pnl.currency_code = pvh.currency_code
  AND pnl.dim_balance_date = pvh.dim_balance_date
WHERE
  pag.type = 'all'
GROUP BY
  pag.portfolio_id,
  p.base_currency_code
Lineage · adib_rm.sdk_portfolio_chart_mv 6 objects
Direct (1-hop) dependencies from rw_depend, across schemas. Click a neighbor to expand its dependencies; ⌘/Ctrl-click opens its page. Drag to pan, scroll to zoom. External source/sink endpoints (Kafka, Iceberg) are not shown.
RWM Console cluster: risingwave-adib.adib-rw.svc.cluster.local
