CREATE MATERIALIZED VIEW insights.pnl_summary_mv AS
SELECT
b.account_group_id,
b.dim_balance_date,
b.position_type,
b.currency_code,
b.market_value,
b.total_average_cost,
b.fair_value,
b.market_value_system_currency,
b.total_average_cost_system_currency,
b.fair_value_system_currency,
COALESCE(t.cumulative_adjusted_netflow, 0) AS cumulative_adjusted_netflow,
COALESCE(t.cumulative_adjusted_netflow_system_currency, 0) AS cumulative_adjusted_netflow_system_currency,
(
b.market_value - COALESCE(t.cumulative_adjusted_netflow, 0)
) AS total_gain_loss,
(
b.market_value - b.total_average_cost
) AS unrealized_gain_loss,
(
b.market_value - COALESCE(t.cumulative_adjusted_netflow, 0) - (
b.market_value - b.total_average_cost
)
) AS realized_gain_loss,
(
b.market_value_system_currency - COALESCE(t.cumulative_adjusted_netflow_system_currency, 0)
) AS total_gain_loss_system_currency,
(
b.market_value_system_currency - b.total_average_cost_system_currency
) AS unrealized_gain_loss_system_currency,
(
b.market_value_system_currency - COALESCE(t.cumulative_adjusted_netflow_system_currency, 0) - (
b.market_value_system_currency - b.total_average_cost_system_currency
)
) AS realized_gain_loss_system_currency,
(
b.fair_value - COALESCE(t.cumulative_adjusted_netflow, 0)
) AS fair_value_total_gain_loss,
(
b.fair_value - b.total_average_cost
) AS fair_value_unrealized_gain_loss,
(
b.fair_value - COALESCE(t.cumulative_adjusted_netflow, 0) - (
b.fair_value - b.total_average_cost
)
) AS fair_value_realized_gain_loss,
(
b.fair_value_system_currency - COALESCE(t.cumulative_adjusted_netflow_system_currency, 0)
) AS fair_value_total_gain_loss_system_currency,
(
b.fair_value_system_currency - b.total_average_cost_system_currency
) AS fair_value_unrealized_gain_loss_system_currency,
(
b.fair_value_system_currency - COALESCE(t.cumulative_adjusted_netflow_system_currency, 0) - (
b.fair_value_system_currency - b.total_average_cost_system_currency
)
) AS fair_value_realized_gain_loss_system_currency
FROM insights.position_summary_mv AS b
ASOF LEFT JOIN insights.flow_summary_mv AS t
ON b.account_group_id = t.account_group_id
AND b.position_type = t.position_type
AND b.currency_code = t.currency_code
AND t.dim_transaction_date <= b.dim_balance_date
JOIN insights.account_groups_mv FOR SYSTEM_TIME AS OF PROCTIME() AS ag
ON b.account_group_id = ag.account_group_id
WHERE
ag.source_entity_type <> 'user'