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
306
State size
1.6 MiB
Created
2026-08-26 08:36
Initialized
2026-08-26 08:36
Fragment flags
MVIEWNOWSNAPSHOT_BACKFILL_STREAM_SCANSTREAM_SCAN
Actors
Actor	Fragment	Worker	State
131857	26003	26	running
131858	26003	26	running
131859	26004	26	running
131860	26004	26	running
131861	25982	26	running
131862	25982	26	running
131863	25983	26	running
131864	25983	26	running
131865	25987	26	running
131866	25987	26	running
131867	26018	26	running
131868	26018	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
