CREATE MATERIALIZED VIEW insights.account_groups_mv AS
SELECT
'account_group_' || MD5(CAST((
oa.account_id || 'all'
) AS BYTEA)) AS account_group_id,
oa.base_currency_code AS base_currency,
oa.opening_date,
'account' AS source_entity_type
FROM insights.open_accounts_mv AS oa
UNION ALL
SELECT
'account_group_' || MD5(CAST((
c.id || 'all'
) AS BYTEA)) AS account_group_id,
c.base_currency_code AS base_currency,
c.onboarding_date AS opening_date,
'client' AS source_entity_type
FROM olap.clients_dm AS c
JOIN insights.clients_with_accounts_mv AS cwa
ON cwa.client_id = c.id
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
c.id || 'restricted'
) AS BYTEA)) AS account_group_id,
c.base_currency_code AS base_currency,
c.onboarding_date AS opening_date,
'client' AS source_entity_type
FROM olap.clients_dm AS c
JOIN insights.clients_with_accounts_mv AS cwa
ON cwa.client_id = c.id
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
c.id || 'un_restricted'
) AS BYTEA)) AS account_group_id,
c.base_currency_code AS base_currency,
c.onboarding_date AS opening_date,
'client' AS source_entity_type
FROM olap.clients_dm AS c
JOIN insights.clients_with_accounts_mv AS cwa
ON cwa.client_id = c.id
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
p.portfolio_id || 'all'
) AS BYTEA)) AS account_group_id,
p.base_currency_code AS base_currency,
p.opening_date,
'portfolio' AS source_entity_type
FROM olap.portfolios_dm AS p
JOIN insights.portfolios_with_accounts_mv AS pwa
ON pwa.portfolio_id = p.portfolio_id
WHERE
p.disabled_at IS NULL AND NOT p.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
p.portfolio_id || 'restricted'
) AS BYTEA)) AS account_group_id,
p.base_currency_code AS base_currency,
p.opening_date,
'portfolio' AS source_entity_type
FROM olap.portfolios_dm AS p
JOIN insights.portfolios_with_accounts_mv AS pwa
ON pwa.portfolio_id = p.portfolio_id
WHERE
p.disabled_at IS NULL AND NOT p.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
p.portfolio_id || 'un_restricted'
) AS BYTEA)) AS account_group_id,
p.base_currency_code AS base_currency,
p.opening_date,
'portfolio' AS source_entity_type
FROM olap.portfolios_dm AS p
JOIN insights.portfolios_with_accounts_mv AS pwa
ON pwa.portfolio_id = p.portfolio_id
WHERE
p.disabled_at IS NULL AND NOT p.m_is_stub IS TRUE
UNION ALL
SELECT
'account_group_' || MD5(CAST((
uam.user_id || uam.type
) AS BYTEA)) AS account_group_id,
u.base_currency_code AS base_currency,
MIN(oa.opening_date) AS opening_date,
'user' AS source_entity_type
FROM insights.user_account_membership_mv AS uam
JOIN olap.users_dm AS u
ON u.user_id = uam.user_id
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = uam.account_id
GROUP BY
uam.user_id,
uam.type,
u.base_currency_code
UNION ALL
SELECT
'account_group_' || MD5(CAST((
pad.party_id || pad.type
) AS BYTEA)) AS account_group_id,
MIN(lp.base_currency_code) AS base_currency,
MIN(oa.opening_date) AS opening_date,
'party' AS source_entity_type
FROM insights.party_account_direct_mv AS pad
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = pad.account_id
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = pad.customer_relationship_id
AND lp.disabled_at IS NULL
GROUP BY
pad.party_id,
pad.type