CREATE MATERIALIZED VIEW adib_rm.client_portfolios_mv
WITH (
backfill_order=FIXED(olap.service_types_dm -> olap.clients_dm)
) AS
SELECT
c.id AS client_id,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'id',
p.portfolio_id,
'name',
p.name,
'number',
p.number,
'serviceTypeId',
p.service_type_id,
'serviceType',
st.type,
'marketValue',
JSONB_BUILD_OBJECT('amount', CAST(ps.market_value AS VARCHAR), 'currencyCode', p.base_currency_code),
'marketValueSystemCurrency',
JSONB_BUILD_OBJECT(
'amount',
CAST(ps.market_value_system_currency AS VARCHAR),
'currencyCode',
c.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 p.portfolio_id
) AS portfolios
FROM olap.clients_dm AS c
JOIN olap.clients_portfolios_dm AS cp
ON cp.client_id = c.id
AND cp.disabled_at IS NULL
AND cp.effective_start_date <= CURRENT_TIMESTAMP
AND cp.effective_end_date IS NULL
JOIN adib_rm.portfolios_plain_mv AS p
ON p.portfolio_id = cp.portfolio_id AND p.closing_date IS NULL
JOIN olap.service_types_dm FOR SYSTEM_TIME AS OF PROCTIME() AS st
ON st.service_type_id = p.service_type_id
LEFT JOIN insights.portfolio_to_account_groups_mv AS pag
ON pag.portfolio_id = p.portfolio_id AND pag.type = 'all'
LEFT JOIN insights.position_snapshot_mv AS ps
ON ps.account_group_id = pag.account_group_id
AND ps.position_type = 'POSITION'
AND ps.currency_code = p.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
ON pnl.account_group_id = ps.account_group_id
AND pnl.position_type = ps.position_type
AND pnl.currency_code = ps.currency_code
WHERE
c.disabled_at IS NULL
GROUP BY
c.id