CREATE MATERIALIZED VIEW insights.pnl_by_currency_mv AS
SELECT
b.account_group_id,
b.dim_balance_date,
b.position_type,
b.item_id AS holding_currency_code,
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(cn.cumulative_adjusted_netflow, 0) AS cumulative_adjusted_netflow,
COALESCE(cn.cumulative_adjusted_netflow_system_currency, 0) AS cumulative_adjusted_netflow_system_currency,
(
b.market_value - COALESCE(cn.cumulative_adjusted_netflow, 0)
) AS total_gain_loss,
(
b.market_value - b.total_average_cost
) AS unrealized_gain_loss,
(
b.market_value - COALESCE(cn.cumulative_adjusted_netflow, 0) - (
b.market_value - b.total_average_cost
)
) AS realized_gain_loss,
(
b.market_value_system_currency - COALESCE(cn.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(cn.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(cn.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(cn.cumulative_adjusted_netflow, 0) - (
b.fair_value - b.total_average_cost
)
) AS fair_value_realized_gain_loss,
(
b.fair_value_system_currency - COALESCE(cn.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(cn.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_by_currency_mv AS b
ASOF LEFT JOIN insights.flow_by_currency_mv AS cn
ON b.account_group_id = cn.account_group_id
AND b.item_id = cn.holding_currency_code
AND b.position_type = cn.position_type
AND b.currency_code = cn.currency_code
AND cn.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'