CREATE MATERIALIZED VIEW alinma_bff.advisor_kpi_account_value_buckets_mv AS
SELECT
account_id,
CAST('WEEKLY' AS VARCHAR) AS period_kind,
period_start,
aum,
deposits,
financing
FROM (
SELECT
account_id,
CAST(DATE_TRUNC('WEEK', dim_value_date) AS DATE) AS period_start,
aum,
deposits,
financing,
ROW_NUMBER() OVER (
PARTITION BY account_id, CAST(DATE_TRUNC('WEEK', dim_value_date) AS DATE)
ORDER BY dim_value_date DESC
) AS rn
FROM alinma_bff.advisor_kpi_account_value_daily_mv AS advisor_kpi_account_value_daily_mv_next
WHERE
CAST(dim_value_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '400 DAYS'
) AS w
WHERE
w.rn = 1
UNION ALL
SELECT
account_id,
CAST('MONTHLY' AS VARCHAR) AS period_kind,
period_start,
aum,
deposits,
financing
FROM alinma_bff.advisor_kpi_account_value_monthly_mv AS advisor_kpi_account_value_monthly_mv_next
UNION ALL
SELECT
account_id,
CAST('QUARTERLY' AS VARCHAR) AS period_kind,
period_start,
aum,
deposits,
financing
FROM (
SELECT
account_id,
CAST(DATE_TRUNC('QUARTER', period_start) AS DATE) AS period_start,
aum,
deposits,
financing,
ROW_NUMBER() OVER (
PARTITION BY account_id, CAST(DATE_TRUNC('QUARTER', period_start) AS DATE)
ORDER BY period_start DESC
) AS rn
FROM alinma_bff.advisor_kpi_account_value_monthly_mv AS advisor_kpi_account_value_monthly_mv_next
) AS q
WHERE
q.rn = 1
UNION ALL
SELECT
account_id,
CAST('YEARLY' AS VARCHAR) AS period_kind,
period_start,
aum,
deposits,
financing
FROM (
SELECT
account_id,
CAST(DATE_TRUNC('YEAR', period_start) AS DATE) AS period_start,
aum,
deposits,
financing,
ROW_NUMBER() OVER (
PARTITION BY account_id, CAST(DATE_TRUNC('YEAR', period_start) AS DATE)
ORDER BY period_start DESC
) AS rn
FROM alinma_bff.advisor_kpi_account_value_monthly_mv AS advisor_kpi_account_value_monthly_mv_next
) AS y
WHERE
y.rn = 1