CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_portfolio_growth_mv AS
WITH latest AS (
SELECT
portfolio_id,
latest_date,
trv_latest,
na_latest
FROM (
SELECT
portfolio_id,
dim_balance_date AS latest_date,
relationship_value AS trv_latest,
net_assets AS na_latest,
ROW_NUMBER() OVER (PARTITION BY portfolio_id ORDER BY dim_balance_date DESC) AS rn
FROM alinma_bff.advisor_kpi_portfolio_daily_mv AS advisor_kpi_portfolio_daily_mv_next
) AS ranked
WHERE
ranked.rn = 1
), boundaries AS (
SELECT
d.portfolio_id,
d.dim_balance_date,
d.relationship_value,
d.net_assets,
l.latest_date
FROM alinma_bff.advisor_kpi_portfolio_daily_mv AS d
JOIN latest AS l
ON l.portfolio_id = d.portfolio_id
), weekly_start AS (
SELECT
portfolio_id,
relationship_value AS trv_start,
net_assets AS na_start
FROM (
SELECT
portfolio_id,
relationship_value,
net_assets,
ROW_NUMBER() OVER (PARTITION BY portfolio_id ORDER BY dim_balance_date DESC) AS rn
FROM boundaries
WHERE
dim_balance_date < CAST(DATE_TRUNC('WEEK', latest_date) AS DATE)
) AS t
WHERE
t.rn = 1
), monthly_start AS (
SELECT
portfolio_id,
relationship_value AS trv_start,
net_assets AS na_start
FROM (
SELECT
portfolio_id,
relationship_value,
net_assets,
ROW_NUMBER() OVER (PARTITION BY portfolio_id ORDER BY dim_balance_date DESC) AS rn
FROM boundaries
WHERE
dim_balance_date < CAST(DATE_TRUNC('MONTH', latest_date) AS DATE)
) AS t
WHERE
t.rn = 1
), quarterly_start AS (
SELECT
portfolio_id,
relationship_value AS trv_start,
net_assets AS na_start
FROM (
SELECT
portfolio_id,
relationship_value,
net_assets,
ROW_NUMBER() OVER (PARTITION BY portfolio_id ORDER BY dim_balance_date DESC) AS rn
FROM boundaries
WHERE
dim_balance_date < CAST(DATE_TRUNC('QUARTER', latest_date) AS DATE)
) AS t
WHERE
t.rn = 1
), yearly_start AS (
SELECT
portfolio_id,
relationship_value AS trv_start,
net_assets AS na_start
FROM (
SELECT
portfolio_id,
relationship_value,
net_assets,
ROW_NUMBER() OVER (PARTITION BY portfolio_id ORDER BY dim_balance_date DESC) AS rn
FROM boundaries
WHERE
dim_balance_date < CAST(DATE_TRUNC('YEAR', latest_date) AS DATE)
) AS t
WHERE
t.rn = 1
)
SELECT
l.portfolio_id,
l.trv_latest - COALESCE(ws.trv_start, 0) AS trv_growth_weekly,
l.trv_latest - COALESCE(ms.trv_start, 0) AS trv_growth_monthly,
l.trv_latest - COALESCE(qs.trv_start, 0) AS trv_growth_quarterly,
l.trv_latest - COALESCE(ys.trv_start, 0) AS trv_growth_yearly,
l.na_latest - COALESCE(ws.na_start, 0) AS net_assets_growth_weekly,
l.na_latest - COALESCE(ms.na_start, 0) AS net_assets_growth_monthly,
l.na_latest - COALESCE(qs.na_start, 0) AS net_assets_growth_quarterly,
l.na_latest - COALESCE(ys.na_start, 0) AS net_assets_growth_yearly
FROM latest AS l
LEFT JOIN weekly_start AS ws
ON ws.portfolio_id = l.portfolio_id
LEFT JOIN monthly_start AS ms
ON ms.portfolio_id = l.portfolio_id
LEFT JOIN quarterly_start AS qs
ON qs.portfolio_id = l.portfolio_id
LEFT JOIN yearly_start AS ys
ON ys.portfolio_id = l.portfolio_id