CREATE MATERIALIZED VIEW insights.twrr_mv AS
WITH daily_balances AS (
SELECT
b.account_group_id,
dim_balance_date,
position_type,
currency_code,
market_value,
fair_value,
market_value_system_currency,
fair_value_system_currency,
LAG(market_value) OVER w AS prev_market_value,
LAG(fair_value) OVER w AS prev_fair_value,
LAG(market_value_system_currency) OVER w AS prev_market_value_system_currency,
LAG(fair_value_system_currency) OVER w AS prev_fair_value_system_currency
FROM insights.position_summary_mv AS b
JOIN insights.account_groups_mv FOR SYSTEM_TIME AS OF PROCTIME() AS ag
ON b.account_group_id = ag.account_group_id
WHERE
position_type = 'POSITION' AND ag.source_entity_type = 'portfolio'
WINDOW w AS (
PARTITION BY b.account_group_id, currency_code, position_type
ORDER BY dim_balance_date
)
), twrr_data AS (
SELECT
b.account_group_id,
b.dim_balance_date,
b.position_type,
b.currency_code,
b.market_value,
b.prev_market_value,
b.fair_value,
b.prev_fair_value,
b.market_value_system_currency,
b.prev_market_value_system_currency,
b.fair_value_system_currency,
b.prev_fair_value_system_currency,
COALESCE(t.adjusted_net_flow, 0) AS net_flow,
COALESCE(t.adjusted_net_flow_system_currency, 0) AS net_flow_system_currency
FROM daily_balances AS b
LEFT JOIN insights.flow_summary_mv AS t
ON b.account_group_id = t.account_group_id
AND b.dim_balance_date = t.dim_transaction_date
AND b.position_type = t.position_type
AND b.currency_code = t.currency_code
)
SELECT
account_group_id,
dim_balance_date,
position_type,
currency_code,
market_value,
prev_market_value,
net_flow,
CASE
WHEN prev_market_value IS NULL OR prev_market_value = 0
THEN 0
WHEN market_value = 0
THEN 0
WHEN market_value - net_flow < 0 AND prev_market_value + net_flow > 0
THEN GREATEST(market_value / (
prev_market_value + net_flow
) - 1, -0.999999999999999999)
ELSE GREATEST((
market_value - net_flow
) / prev_market_value - 1, -0.999999999999999999)
END AS daily_subperiod_return,
CASE
WHEN prev_fair_value IS NULL OR prev_fair_value = 0
THEN 0
WHEN fair_value = 0
THEN 0
WHEN fair_value - net_flow < 0 AND prev_fair_value + net_flow > 0
THEN GREATEST(fair_value / (
prev_fair_value + net_flow
) - 1, -0.999999999999999999)
ELSE GREATEST((
fair_value - net_flow
) / prev_fair_value - 1, -0.999999999999999999)
END AS daily_subperiod_fair_value_return,
CASE
WHEN prev_market_value_system_currency IS NULL
OR prev_market_value_system_currency = 0
THEN 0
WHEN market_value_system_currency = 0
THEN 0
WHEN market_value_system_currency - net_flow_system_currency < 0
AND prev_market_value_system_currency + net_flow_system_currency > 0
THEN GREATEST(
market_value_system_currency / (
prev_market_value_system_currency + net_flow_system_currency
) - 1,
-0.999999999999999999
)
ELSE GREATEST(
(
market_value_system_currency - net_flow_system_currency
) / prev_market_value_system_currency - 1,
-0.999999999999999999
)
END AS daily_subperiod_return_system_currency,
CASE
WHEN prev_fair_value_system_currency IS NULL OR prev_fair_value_system_currency = 0
THEN 0
WHEN fair_value_system_currency = 0
THEN 0
WHEN fair_value_system_currency - net_flow_system_currency < 0
AND prev_fair_value_system_currency + net_flow_system_currency > 0
THEN GREATEST(
fair_value_system_currency / (
prev_fair_value_system_currency + net_flow_system_currency
) - 1,
-0.999999999999999999
)
ELSE GREATEST(
(
fair_value_system_currency - net_flow_system_currency
) / prev_fair_value_system_currency - 1,
-0.999999999999999999
)
END AS daily_subperiod_fair_value_return_system_currency
FROM twrr_data
WHERE
NOT prev_market_value IS NULL