CREATE MATERIALIZED VIEW insights.client_to_account_groups_mv AS
SELECT
c.id AS client_id,
'account_group_' || MD5(CAST((
c.id || 'all'
) AS BYTEA)) AS account_group_id,
CAST('all' AS VARCHAR) AS type
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
c.id AS client_id,
'account_group_' || MD5(CAST((
c.id || 'restricted'
) AS BYTEA)) AS account_group_id,
CAST('restricted' AS VARCHAR) AS type
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
c.id AS client_id,
'account_group_' || MD5(CAST((
c.id || 'un_restricted'
) AS BYTEA)) AS account_group_id,
CAST('un_restricted' AS VARCHAR) AS type
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE
UNION ALL
SELECT
c.id AS client_id,
'account_group_' || MD5(CAST((
c.id || 'none'
) AS BYTEA)) AS account_group_id,
CAST('none' AS VARCHAR) AS type
FROM olap.clients_dm AS c
WHERE
c.closing_date IS NULL AND NOT c.m_is_stub IS TRUE