CREATE MATERIALIZED VIEW alinma_bff.party_advisor_kpi_period_adds_mv AS
WITH windows AS (
SELECT
user_id,
1 AS pw,
0 AS pm,
0 AS pq,
0 AS py,
0 AS cw,
0 AS cm,
0 AS cq,
0 AS cy
FROM alinma_bff.party_advisor_kpi_recent_portfolio_users_mv AS party_advisor_kpi_recent_portfolio_users_mv_next
WHERE
CAST(opening_date AS TIMESTAMPTZ) >= DATE_TRUNC('WEEK', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
1,
0,
0,
0,
0,
0,
0
FROM alinma_bff.party_advisor_kpi_recent_portfolio_users_mv AS party_advisor_kpi_recent_portfolio_users_mv_next
WHERE
CAST(opening_date AS TIMESTAMPTZ) >= DATE_TRUNC('MONTH', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
1,
0,
0,
0,
0,
0
FROM alinma_bff.party_advisor_kpi_recent_portfolio_users_mv AS party_advisor_kpi_recent_portfolio_users_mv_next
WHERE
CAST(opening_date AS TIMESTAMPTZ) >= DATE_TRUNC('QUARTER', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
0,
1,
0,
0,
0,
0
FROM alinma_bff.party_advisor_kpi_recent_portfolio_users_mv AS party_advisor_kpi_recent_portfolio_users_mv_next
WHERE
CAST(opening_date AS TIMESTAMPTZ) >= DATE_TRUNC('YEAR', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
0,
0,
1,
0,
0,
0
FROM alinma_bff.party_advisor_kpi_recent_customer_users_mv
WHERE
CAST(onboarding_date AS TIMESTAMPTZ) >= DATE_TRUNC('WEEK', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
0,
0,
0,
1,
0,
0
FROM alinma_bff.party_advisor_kpi_recent_customer_users_mv
WHERE
CAST(onboarding_date AS TIMESTAMPTZ) >= DATE_TRUNC('MONTH', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
0,
0,
0,
0,
1,
0
FROM alinma_bff.party_advisor_kpi_recent_customer_users_mv
WHERE
CAST(onboarding_date AS TIMESTAMPTZ) >= DATE_TRUNC('QUARTER', CURRENT_TIMESTAMP)
UNION ALL
SELECT
user_id,
0,
0,
0,
0,
0,
0,
0,
1
FROM alinma_bff.party_advisor_kpi_recent_customer_users_mv
WHERE
CAST(onboarding_date AS TIMESTAMPTZ) >= DATE_TRUNC('YEAR', CURRENT_TIMESTAMP)
)
SELECT
user_id,
CAST(SUM(pw) AS INT) AS portfolios_added_weekly,
CAST(SUM(pm) AS INT) AS portfolios_added_monthly,
CAST(SUM(pq) AS INT) AS portfolios_added_quarterly,
CAST(SUM(py) AS INT) AS portfolios_added_yearly,
CAST(SUM(cw) AS INT) AS clients_added_weekly,
CAST(SUM(cm) AS INT) AS clients_added_monthly,
CAST(SUM(cq) AS INT) AS clients_added_quarterly,
CAST(SUM(cy) AS INT) AS clients_added_yearly
FROM windows
GROUP BY
user_id