Holistics-postgres public.trybers pivoted acknowledged providers materialized view
DISPLAYTITLE:Holistics-postgres public.trybers_pivoted_acknowledged_providers materialized view
Query
with
tryber_rows as (
select
id,
wp_user_id
from
wp_appq_evd_profile
where
wp_user_id not in (select distinct wp_user_id from wp_appq_user_to_customer)
),
acknowledged_providers_data as (
select
wp_appq_custom_user_field_data.profile_id,
wp_appq_custom_user_field_groups.name as provider_group,
wp_appq_custom_user_field.name as provider_type,
wp_appq_custom_user_field_extras.name as provider_name
from
wp_appq_custom_user_field_data
left join wp_appq_custom_user_field_extras on wp_appq_custom_user_field_extras.id::text = wp_appq_custom_user_field_data.value
left join wp_appq_custom_user_field on wp_appq_custom_user_field_extras.custom_user_field_id = wp_appq_custom_user_field.id
left join wp_appq_custom_user_field_groups on wp_appq_custom_user_field.custom_user_field_group_id = wp_appq_custom_user_field_groups.id
where
wp_appq_custom_user_field_groups.id in (1, 2, 4, 5, 9) -- are 'providers'
and wp_appq_custom_user_field_extras.name is not null -- are 'acknowledged'
)
select
t.id as tester_id,
array_agg(provider_name order by provider_name) filter (where provider_type = 'Fornitore Energia Elettrica') as "Fornitore Energia Elettrica",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Piattaforme di Streaming') as "Piattaforme di Streaming",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Conto Corrente Privato') as "Conto Corrente Privato",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Operatore Internet Domestico') as "Operatore Internet Domestico",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Operatore Mobile') as "Operatore Mobile",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Fornitore Gas') as "Fornitore Gas",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Assicurazioni') as "Assicurazioni",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Tipologie di assicurazione') as "Tipologie di assicurazione",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Piattaforme musicali') as "Piattaforme musicali",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Conto Corrente Business') as "Conto Corrente Business",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Servizi Criptovalute') as "Servizi Criptovalute",
array_agg(provider_name order by provider_name) filter (where provider_type = 'Strumenti di Pagamento') as "Strumenti di Pagamento"
from
tryber_rows as t
left join acknowledged_providers_data a on t.id = a.profile_id
group by
tester_id
Keys and Indexes
CREATE UNIQUE INDEX tryberspivotedacknowledgedproviders_index_testerid ON public.trybers_pivoted_acknowledged_providers (tester_id);