Holistics-postgres public.trybers augmentation materialized view
DISPLAYTITLE:Holistics-postgres public.trybers_augmentation materialized view
Query
-- cp.title as referral_campaign_title
-- course data -- training campaigns data -- business campaigns data -- bug severity data -- referral data -- small group data
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)
),
courses_data as (
select
cc.tester_id,
coalesce(max(cc.is_completed) filter(where c.career = 'General' and c.level = 1), 0) as general_course_lv1_entry_test_completion,
max(cc.completion_date) filter(where c.career = 'General' and c.level = 1) as general_course_lv1_entry_test_completion_date,
coalesce(max(cc.is_completed) filter(where c.career = 'Functional' and c.level = 1), 0) as functional_course_lv1_completion,
max(cc.completion_date) filter(where c.career = 'Functional' and c.level = 1) as functional_course_lv1_completion_date,
coalesce(max(cc.is_completed) filter(where c.career = 'UX' and c.level = 1), 0) as ux_course_lv1_completion,
max(cc.completion_date) filter(where c.career = 'UX' and c.level = 1) as ux_course_lv1_completion_date,
coalesce(max(cc.is_completed) filter(where c.career = 'General' and c.level = 2), 0) as general_course_lv2_tools_completion,
max(cc.completion_date) filter(where c.career = 'General' and c.level = 2) as general_course_lv2_tools_completion_date
from
wp_appq_course_tester_status as cc
left join wp_appq_course as c on cc.course_id = c.id
group by
tester_id
),
training_campaign_data as (
select
tester_id,
count(cp.id) filter(where cp.family = 'UX') as n_ux_training_campaigns,
max(cp.start_date) filter(where cp.family = 'UX') as ux_last_training_campaign_start_date,
count(cp.id) filter(where cp.family = 'Functional') as n_functional_training_campaigns,
max(cp.start_date) filter(where cp.family = 'Functional') as functional_last_training_campaign_start_date
from
wp_appq_exp_points as exp
left join wp_appq_evd_campaign as cp on exp.campaign_id = cp.id
where
exp.activity_id = 1
and exp.amount > 0
and cp.class = 'Training'
group by
tester_id
),
business_campaign_data as (
select
tester_id,
count(cp.id) filter(where cp.family = 'Functional') as n_functional_business_campaigns,
max(cp.start_date) filter(where cp.family = 'Functional') as functional_last_business_campaign_start_date,
count(cp.id) filter(where cp.family = 'UX') as n_ux_business_campaigns,
max(cp.start_date) filter(where cp.family = 'UX') as ux_last_business_campaign_start_date,
count(cp.id) filter(where cp.family = 'Ethical Hacking') as n_eth_business_campaigns,
max(cp.start_date) filter(where cp.family = 'Ethical Hacking') as eth_last_business_campaign_start_date
from
wp_appq_exp_points as exp
left join wp_appq_evd_campaign as cp on exp.campaign_id = cp.id
where
exp.activity_id = 1
and exp.amount > 0
and cp.class = 'Business'
group by
tester_id
),
bug_severity_data as (
select
wp_user_id as user_id,
count(id) filter(where severity_id = 1) as n_bug_found_low_severity,
count(id) filter(where severity_id = 2) as n_bug_found_medium_severity,
count(id) filter(where severity_id = 3) as n_bug_found_high_severity,
count(id) filter(where severity_id = 4) as n_bug_found_critical_severity
from
wp_appq_evd_bug
where
status_id = 2
group by
wp_user_id
),
referral_data as (
select
ref.tester_id,
ref.referrer_id,
/*
case
when p.name = 'Deleted User' then p.name
else concat(p.surname, ' ', p.name)
end as referrer_full_name,
*/
ref.campaign_id as referral_campaign_id
from
wp_appq_referral_data as ref
left join wp_appq_evd_profile as p on ref.referrer_id = p.id
left join wp_appq_evd_campaign as cp on ref.campaign_id = cp.id
)
/*
small_group as (
select
sg.tester_id,
class.campaign_family,
count(sg.id) as n_campaigns_in_small_group
from
wp_appq_lc_access as sg
left join wp_appq_evd_campaign as cp on sg.view_id = cp.page_preview_id
left join campaign_classification as class on cp.id = class.campaign_id
group by
sg.tester_id,
class.campaign_family
),
*/
select
p.id,
p.wp_user_id,
case coalesce(cd.general_course_lv1_entry_test_completion, 0)
when 1 then TRUE
else FALSE
end as general_course_lv1_entry_test_completion,
cd.general_course_lv1_entry_test_completion_date,
case coalesce(cd.functional_course_lv1_completion, 0)
when 1 then TRUE
else FALSE
end as functional_course_lv1_completion,
cd.functional_course_lv1_completion_date,
case coalesce(cd.ux_course_lv1_completion, 0)
when 1 then TRUE
else FALSE
end as ux_course_lv1_completion,
cd.ux_course_lv1_completion_date,
case coalesce(cd.general_course_lv2_tools_completion, 0)
when 1 then TRUE
else FALSE
end as general_course_lv2_tools_completion,
cd.general_course_lv2_tools_completion_date,
coalesce(tcd.n_functional_training_campaigns, 0) as n_functional_training_campaigns,
tcd.functional_last_training_campaign_start_date,
coalesce(tcd.n_ux_training_campaigns, 0) as n_ux_training_campaigns,
tcd.ux_last_training_campaign_start_date,
coalesce(bcd.n_ux_business_campaigns, 0) as n_ux_business_campaigns,
bcd.ux_last_business_campaign_start_date,
coalesce(bcd.n_functional_business_campaigns, 0) as n_functional_business_campaigns,
bcd.functional_last_business_campaign_start_date,
coalesce(bcd.n_eth_business_campaigns, 0) as n_eth_business_campaigns,
bcd.eth_last_business_campaign_start_date,
coalesce(bsd.n_bug_found_low_severity, 0) as n_bug_found_low_severity,
coalesce(bsd.n_bug_found_medium_severity, 0) as n_bug_found_medium_severity,
coalesce(bsd.n_bug_found_high_severity, 0) as n_bug_found_high_severity,
coalesce(bsd.n_bug_found_critical_severity, 0) as n_bug_found_critical_severity,
rd.referrer_id,
rd.referral_campaign_id
from
tryber_rows as p
left join courses_data as cd on p.id = cd.tester_id
left join training_campaign_data as tcd on p.id = tcd.tester_id
left join business_campaign_data as bcd on p.id = bcd.tester_id
left join bug_severity_data as bsd on p.wp_user_id = bsd.user_id
left join referral_data as rd on p.id = rd.tester_id
Keys and Indexes
CREATE UNIQUE INDEX trybersaugmentation_uniqueindex_id ON public.trybers_augmentation (id);