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.client_account_group_types_mv AS cagt
ON cagt.client_id = c.id AND cagt.type = 'all'
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.client_account_group_types_mv AS cagt
ON cagt.client_id = c.id AND cagt.type = 'restricted'
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.client_account_group_types_mv AS cagt
ON cagt.client_id = c.id AND cagt.type = 'un_restricted'
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.portfolio_account_group_types_mv AS pagt
ON pagt.portfolio_id = p.portfolio_id AND pagt.type = 'all'
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.portfolio_account_group_types_mv AS pagt
ON pagt.portfolio_id = p.portfolio_id AND pagt.type = 'restricted'
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.portfolio_account_group_types_mv AS pagt
ON pagt.portfolio_id = p.portfolio_id AND pagt.type = 'un_restricted'
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((
pa.party_id || pa.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 (
SELECT
party_id,
customer_relationship_id,
account_id,
type
FROM insights.party_account_direct_mv AS party_account_direct_mv_next
UNION ALL
SELECT
party_id,
customer_relationship_id,
account_id,
type
FROM insights.party_account_via_portfolio_mv AS party_account_via_portfolio_mv_next
) AS pa
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = pa.account_id
LEFT JOIN party.lifecycle_profiles AS lp
ON lp.customer_relationship_id = pa.customer_relationship_id
AND lp.disabled_at IS NULL
GROUP BY
pa.party_id,
pa.type
HAVING
NOT MIN(lp.base_currency_code) IS NULL