CREATE MATERIALIZED VIEW alpheya_experience_bff.account_latest_facts_mv AS
SELECT
inv.account_id,
inv.available_balance,
inv.hold_balance,
inv.purchasing_power,
CAST(NULL AS DECIMAL) AS profit_rate,
CAST(NULL AS DECIMAL) AS profit_accrued,
CAST(NULL AS DECIMAL) AS principal,
CAST(NULL AS VARCHAR) AS auto_renewal_status,
CAST(NULL AS DECIMAL) AS credit_limit,
CAST(NULL AS DECIMAL) AS last_statement_balance,
CAST(NULL AS DECIMAL) AS minimum_payment_due,
CAST(NULL AS DATE) AS payment_due_date,
CAST(NULL AS DECIMAL) AS financing_limit,
CAST(NULL AS DECIMAL) AS outstanding_balance
FROM (
SELECT
account_id,
available_balance,
hold_balance,
purchasing_power,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY fact_date DESC) AS rn
FROM olap.investment_accounts_ft
WHERE
disabled_at IS NULL
) AS inv
WHERE
inv.rn = 1
UNION ALL
SELECT
t.account_id,
t.available_balance,
t.hold_balance,
CAST(NULL AS DECIMAL) AS purchasing_power,
t.profit_rate,
t.profit_accrued,
CAST(NULL AS DECIMAL) AS principal,
CAST(NULL AS VARCHAR) AS auto_renewal_status,
CAST(NULL AS DECIMAL) AS credit_limit,
CAST(NULL AS DECIMAL) AS last_statement_balance,
CAST(NULL AS DECIMAL) AS minimum_payment_due,
CAST(NULL AS DATE) AS payment_due_date,
CAST(NULL AS DECIMAL) AS financing_limit,
CAST(NULL AS DECIMAL) AS outstanding_balance
FROM (
SELECT
account_id,
available_balance,
hold_balance,
profit_rate,
profit_accrued,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY fact_date DESC) AS rn
FROM olap.transactional_accounts_ft
WHERE
disabled_at IS NULL
) AS t
WHERE
t.rn = 1
UNION ALL
SELECT
fd.account_id,
CAST(NULL AS DECIMAL) AS available_balance,
CAST(NULL AS DECIMAL) AS hold_balance,
CAST(NULL AS DECIMAL) AS purchasing_power,
fd.profit_rate,
fd.profit_accrued,
fd.principal,
fd.auto_renewal_status,
CAST(NULL AS DECIMAL) AS credit_limit,
CAST(NULL AS DECIMAL) AS last_statement_balance,
CAST(NULL AS DECIMAL) AS minimum_payment_due,
CAST(NULL AS DATE) AS payment_due_date,
CAST(NULL AS DECIMAL) AS financing_limit,
CAST(NULL AS DECIMAL) AS outstanding_balance
FROM (
SELECT
account_id,
principal,
profit_rate,
profit_accrued,
auto_renewal_status,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY fact_date DESC) AS rn
FROM olap.fixed_deposit_accounts_ft
WHERE
disabled_at IS NULL
) AS fd
WHERE
fd.rn = 1
UNION ALL
SELECT
cc.account_id,
CAST(NULL AS DECIMAL) AS available_balance,
CAST(NULL AS DECIMAL) AS hold_balance,
CAST(NULL AS DECIMAL) AS purchasing_power,
CAST(NULL AS DECIMAL) AS profit_rate,
CAST(NULL AS DECIMAL) AS profit_accrued,
CAST(NULL AS DECIMAL) AS principal,
CAST(NULL AS VARCHAR) AS auto_renewal_status,
cc.credit_limit,
cc.last_statement_balance,
cc.minimum_payment_due,
cc.payment_due_date,
CAST(NULL AS DECIMAL) AS financing_limit,
CAST(NULL AS DECIMAL) AS outstanding_balance
FROM (
SELECT
account_id,
credit_limit,
last_statement_balance,
minimum_payment_due,
payment_due_date,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY fact_date DESC) AS rn
FROM olap.credit_card_accounts_ft
WHERE
disabled_at IS NULL
) AS cc
WHERE
cc.rn = 1
UNION ALL
SELECT
fin.account_id,
CAST(NULL AS DECIMAL) AS available_balance,
CAST(NULL AS DECIMAL) AS hold_balance,
CAST(NULL AS DECIMAL) AS purchasing_power,
fin.profit_rate,
CAST(NULL AS DECIMAL) AS profit_accrued,
CAST(NULL AS DECIMAL) AS principal,
CAST(NULL AS VARCHAR) AS auto_renewal_status,
CAST(NULL AS DECIMAL) AS credit_limit,
CAST(NULL AS DECIMAL) AS last_statement_balance,
CAST(NULL AS DECIMAL) AS minimum_payment_due,
CAST(NULL AS DATE) AS payment_due_date,
fin.financing_limit,
fin.outstanding_balance
FROM (
SELECT
account_id,
profit_rate,
financing_limit,
outstanding_balance,
ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY fact_date DESC) AS rn
FROM olap.financing_accounts_ft
WHERE
disabled_at IS NULL
) AS fin
WHERE
fin.rn = 1