CREATE MATERIALIZED VIEW insights.account_to_account_groups_mv AS
SELECT
windowed.account_id,
windowed.account_group_id,
windowed.effective_start_date,
CASE
WHEN windowed.effective_end_date IS NULL AND windowed.next_start IS NULL
THEN CAST(NULL AS DATE)
WHEN windowed.effective_end_date IS NULL
THEN windowed.next_start
WHEN windowed.next_start IS NULL
THEN windowed.effective_end_date
ELSE LEAST(windowed.effective_end_date, windowed.next_start)
END AS effective_end_date,
ag.base_currency,
ag.opening_date,
ag.source_entity_type
FROM (
SELECT
account_id,
account_group_id,
effective_start_date,
effective_end_date,
LEAD(effective_start_date) OVER (PARTITION BY account_id, account_group_id ORDER BY effective_start_date) AS next_start
FROM (
SELECT
oa.account_id,
'account_group_' || MD5(CAST((
oa.account_id || 'all'
) AS BYTEA)) AS account_group_id,
CAST('1970-01-01' AS DATE) AS effective_start_date,
CAST(NULL AS DATE) AS effective_end_date
FROM insights.open_accounts_mv AS oa
UNION ALL
SELECT
d.account_id,
'account_group_' || MD5(CAST((
d.client_id || d.type
) AS BYTEA)) AS account_group_id,
d.effective_start_date,
d.effective_end_date
FROM insights.client_account_direct_mv AS d
UNION ALL
SELECT
p.account_id,
'account_group_' || MD5(CAST((
p.client_id || p.type
) AS BYTEA)) AS account_group_id,
p.effective_start_date,
p.effective_end_date
FROM insights.client_account_via_portfolio_mv AS p
UNION ALL
SELECT
atp.account_id,
'account_group_' || MD5(CAST((
atp.portfolio_id || 'all'
) AS BYTEA)) AS account_group_id,
atp.effective_start_date,
atp.effective_end_date
FROM olap.account_to_portfolios_dm AS atp
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
UNION ALL
SELECT
atp.account_id,
'account_group_' || MD5(CAST((
atp.portfolio_id || 'restricted'
) AS BYTEA)) AS account_group_id,
atp.effective_start_date,
atp.effective_end_date
FROM olap.account_to_portfolios_dm AS atp
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
AND oa.is_restricted = TRUE
UNION ALL
SELECT
atp.account_id,
'account_group_' || MD5(CAST((
atp.portfolio_id || 'un_restricted'
) AS BYTEA)) AS account_group_id,
atp.effective_start_date,
atp.effective_end_date
FROM olap.account_to_portfolios_dm AS atp
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
AND NOT oa.is_restricted IS TRUE
UNION ALL
SELECT
pad.account_id,
'account_group_' || MD5(CAST((
pad.party_id || pad.type
) AS BYTEA)) AS account_group_id,
pad.effective_start_date,
pad.effective_end_date
FROM insights.party_account_direct_mv AS pad
UNION ALL
SELECT
pvp.account_id,
'account_group_' || MD5(CAST((
pvp.party_id || pvp.type
) AS BYTEA)) AS account_group_id,
pvp.effective_start_date,
pvp.effective_end_date
FROM insights.party_account_via_portfolio_mv AS pvp
) AS raw
) AS windowed
JOIN insights.account_groups_mv AS ag
ON ag.account_group_id = windowed.account_group_id