Holistics-postgres public.tryber has enabled device materialized view
DISPLAYTITLE:Holistics-postgres public.tryber_has_enabled_device 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)
),
enabled_devices as (
select
wp_crowd_appq_device.id,
wp_crowd_appq_device.id_profile,
wp_crowd_appq_device.form_factor,
wp_crowd_appq_device.manufacturer,
wp_crowd_appq_device.model,
wp_crowd_appq_device.operating_system,
wp_crowd_appq_device.os_version,
wp_crowd_appq_device.creation_time,
wp_crowd_appq_device.update_time
from
wp_crowd_appq_device
where
wp_crowd_appq_device.enabled = 1
)
select
t.id as tester_id,
d.id as device_id,
d.form_factor,
d.manufacturer,
d.model,
d.operating_system,
d.os_version,
d.creation_time,
d.update_time,
case
when d.form_factor = 'PC' then concat(d.form_factor, ' ', d.operating_system)
when (d.form_factor = 'Tablet' or d.form_factor = 'Smartphone' or d.form_factor = 'Smartwatch' or d.form_factor = 'Smart-tv') then concat(d.manufacturer, ' ', d.model)
when d.id is null then null
else 'Other'
end as device_display_name
from
tryber_rows as t
left join enabled_devices d on t.id = d.id_profile
Keys and Indexes
CREATE INDEX tryberhasenableddevice_index_testerid ON public.tryber_has_enabled_device (tester_id);
CREATE INDEX tryberhasenableddevice_index_formfactor ON public.tryber_has_enabled_device (form_factor);
CREATE INDEX tryberhasenableddevice_index_manufacturer ON public.tryber_has_enabled_device (manufacturer);
CREATE INDEX tryberhasenableddevice_index_model ON public.tryber_has_enabled_device (model);