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(COALESCE(ips.market_value, ps.market_value) AS VARCHAR),
'currencyCode',
p.base_currency_code
),
'marketValueSystemCurrency',
JSONB_BUILD_OBJECT(
'amount',
CASE
WHEN NOT ips.market_value IS NULL
THEN CAST(ips.market_value_system_currency AS VARCHAR)
ELSE CAST(ps.market_value_system_currency AS VARCHAR)
END,
'currencyCode',
c.base_currency_code
),
'unrealizedGainLoss',
JSONB_BUILD_OBJECT(
'value',
JSONB_BUILD_OBJECT(
'amount',
CASE
WHEN NOT ips.market_value IS NULL
THEN CAST((
ips.market_value - ips.total_average_cost
) AS VARCHAR)
ELSE CAST(pnl.unrealized_gain_loss AS VARCHAR)
END,
'currencyCode',
p.base_currency_code
),
'percentage',
CASE
WHEN NOT ips.market_value IS NULL
THEN CASE
WHEN ips.total_average_cost IS NULL OR ips.total_average_cost = 0
THEN NULL
ELSE CAST((
(
ips.market_value - ips.total_average_cost
) / ips.total_average_cost
) AS VARCHAR)
END
ELSE 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
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 insights.intraday_position_summary_mv AS ips
ON ips.account_group_id = pag.account_group_id
AND ips.position_type = 'POSITION'
AND ips.currency_code = p.base_currency_code
LEFT JOIN adib_rm.pnl_snapshot_mv AS pnl
ON pnl.account_group_id = pag.account_group_id
AND pnl.position_type = 'POSITION'
AND pnl.currency_code = p.base_currency_code
WHERE
c.disabled_at IS NULL
GROUP BY
c.id