CREATE MATERIALIZED VIEW alpheya_experience_bff.advisor_kpis_mv AS
SELECT
cnt.user_id,
cnt.clients_count,
cnt.portfolios_count,
cnt.accounts_count,
COALESCE(val.total_financing, 0) AS total_financing,
COALESCE(val.total_relationship_value, 0) AS total_relationship_value,
COALESCE(val.total_net_assets, 0) AS total_net_assets,
COALESCE(val.total_deposits, 0) AS total_deposits,
COALESCE(val.total_aum, 0) AS total_aum,
CAST('USD' AS VARCHAR) AS currency_code,
COALESCE(val.updated_at, CAST('1970-01-01 00:00:00 UTC' AS TIMESTAMPTZ)) AS updated_at
FROM alpheya_experience_bff.advisor_kpi_counts_mv AS cnt
LEFT JOIN alpheya_experience_bff.advisor_kpi_values_mv AS val
ON val.user_id = cnt.user_id