sdk_portfolio_chart_mv · adib_rm

materialized view · adib_rm.sdk_portfolio_chart_mv Explain plan ▶
Parallelism
2
Actors
75 / 75
running
Distribution
HASH
Rows
1,906
State size
11.8 MiB
Created
2026-08-25 16:49
Initialized
2026-08-25 16:49
Fragment flags
MVIEWNOWSNAPSHOT_BACKFILL_STREAM_SCANSTREAM_SCAN
Actors
Actor	Fragment	Worker	State
114774	24473	26	running
114775	24473	26	running
114776	24474	26	running
114777	24474	26	running
114778	24480	26	running
114779	24480	26	running
114780	24478	26	running
114781	24478	26	running
114782	24500	26	running
114783	24500	26	running
114784	24512	26	running
114785	24512	26	running
+ 63 more actor(s) (75 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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
          ) AS VARCHAR)
        END
      )
    ) ORDER BY pvh.dim_balance_date
  ) AS series
FROM insights.portfolio_to_account_groups_mv AS pag
JOIN olap.portfolios_dm 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 7 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
