WITH tryber_rows AS (
`` SELECT wp_appq_evd_profile.id,
`` wp_appq_evd_profile.wp_user_id
`` FROM wp_appq_evd_profile
`` WHERE NOT (wp_appq_evd_profile.wp_user_id IN ( SELECT DISTINCT wp_appq_user_to_customer.wp_user_id
`` FROM wp_appq_user_to_customer))
`` ), candidate_data AS (
`` SELECT wp_crowd_appq_has_candidate.user_id,
`` count(DISTINCT wp_crowd_appq_has_candidate.campaign_id) AS n_cp_candidate,
`` count(DISTINCT wp_crowd_appq_has_candidate.campaign_id) FILTER (WHERE wp_crowd_appq_has_candidate.accepted = 1) AS n_cp_accepted
`` FROM wp_crowd_appq_has_candidate
`` GROUP BY wp_crowd_appq_has_candidate.user_id
`` ), completed_campaign_data AS (
`` SELECT ep.tester_id AS profile_id,
`` count(ep.id) AS n_campaigns,
`` count(ep.id) FILTER (WHERE cp.family::text = 'Functional'::text) AS n_functional_campaigns,
`` count(ep.id) FILTER (WHERE cp.family::text = 'UX'::text) AS n_ux_campaigns,
`` count(DISTINCT ep.id) FILTER (WHERE cp.family::text = 'Special'::text) AS n_special_campaigns,
`` count(ep.id) FILTER (WHERE cp.family::text = 'Ethical Hacking'::text) AS n_eth_campaigns
`` FROM wp_appq_exp_points ep
`` JOIN wp_appq_evd_campaign cp ON ep.campaign_id = cp.id
`` WHERE ep.activity_id = 1 AND ep.amount > 0::double precision
`` GROUP BY ep.tester_id
`` ), first_campaign_data AS (
`` SELECT rk.tester_id AS profile_id,
`` rk.campaign_id AS first_campaign_id,
`` rk.campaign AS first_campaign,
`` rk.start_date AS first_campaign_start_date,
`` rk.end_date AS first_campaign_end_date,
`` rk.close_date AS first_campaign_close_date
`` FROM ( SELECT ep.tester_id,
`` cp.id AS campaign_id,
`` cp.title AS campaign,
`` cp.start_date,
`` cp.end_date,
`` cp.close_date,
`` rank() OVER (PARTITION BY ep.tester_id ORDER BY cp.start_date, cp.id DESC) AS cp_start_rank
`` FROM wp_appq_exp_points ep
`` LEFT JOIN wp_appq_evd_campaign cp ON ep.campaign_id = cp.id
`` WHERE ep.activity_id = 1 AND ep.amount > 0::double precision) rk
`` WHERE rk.cp_start_rank = 1
`` ), latest_campaign_data AS (
`` SELECT rk.tester_id AS profile_id,
`` rk.campaign_id AS latest_campaign_id,
`` rk.campaign AS latest_campaign,
`` rk.start_date AS latest_campaign_start_date,
`` rk.end_date AS latest_campaign_end_date,
`` rk.close_date AS latest_campaign_close_date
`` FROM ( SELECT ep.tester_id,
`` cp.id AS campaign_id,
`` cp.title AS campaign,
`` cp.start_date,
`` cp.end_date,
`` cp.close_date,
`` rank() OVER (PARTITION BY ep.tester_id ORDER BY cp.start_date DESC, cp.id DESC) AS cp_start_rank
`` FROM wp_appq_exp_points ep
`` LEFT JOIN wp_appq_evd_campaign cp ON ep.campaign_id = cp.id
`` WHERE ep.activity_id = 1 AND ep.amount > 0::double precision) rk
`` WHERE rk.cp_start_rank = 1
`` ), device_data AS (
`` SELECT wp_crowd_appq_device.id_profile AS profile_id,
`` array_agg(
`` CASE
`` WHEN wp_crowd_appq_device.form_factor = 'PC'::text THEN concat(wp_crowd_appq_device.form_factor, ' ', wp_crowd_appq_device.operating_system)
`` WHEN wp_crowd_appq_device.form_factor = 'Tablet'::text OR wp_crowd_appq_device.form_factor = 'Smartphone'::text OR wp_crowd_appq_device.form_factor = 'Smartwatch'::text OR wp_crowd_appq_device.form_factor = 'Smart-tv'::text THEN concat(wp_crowd_appq_device.manufacturer, ' ', wp_crowd_appq_device.model)
`` ELSE 'Other'::text
`` END ORDER BY (
`` CASE
`` WHEN wp_crowd_appq_device.form_factor = 'PC'::text THEN concat(wp_crowd_appq_device.form_factor, ' ', wp_crowd_appq_device.operating_system)
`` WHEN wp_crowd_appq_device.form_factor = 'Tablet'::text OR wp_crowd_appq_device.form_factor = 'Smartphone'::text OR wp_crowd_appq_device.form_factor = 'Smartwatch'::text OR wp_crowd_appq_device.form_factor = 'Smart-tv'::text THEN concat(wp_crowd_appq_device.manufacturer, ' ', wp_crowd_appq_device.model)
`` ELSE 'Other'::text
`` END)) AS devices
`` FROM wp_crowd_appq_device
`` WHERE wp_crowd_appq_device.enabled = 1
`` GROUP BY wp_crowd_appq_device.id_profile
`` ), bug_data AS (
`` SELECT wp_appq_evd_bug.wp_user_id AS user_id,
`` count(wp_appq_evd_bug.id) AS n_bug_found
`` FROM wp_appq_evd_bug
`` WHERE wp_appq_evd_bug.status_id = 2
`` GROUP BY wp_appq_evd_bug.wp_user_id
`` ), experience_points_data AS (
`` SELECT wp_appq_exp_points.tester_id,
`` sum(wp_appq_exp_points.amount) AS total_experience_points,
`` count(wp_appq_exp_points.id) FILTER (WHERE wp_appq_exp_points.amount ≠ 0::double precision) AS valid_records_per_user
`` FROM wp_appq_exp_points
`` GROUP BY wp_appq_exp_points.tester_id
`` ), language_data AS (
`` SELECT has.profile_id,
`` array_agg(DISTINCT lang.display_name[1] ORDER BY (lang.display_name[1])) AS languages
`` FROM wp_appq_profile_has_lang has
`` LEFT JOIN ( SELECT wp_appq_lang.id,
`` string_to_array(wp_appq_lang.display_name, ' '::text) AS display_name
`` FROM wp_appq_lang) lang ON has.language_id = lang.id
`` GROUP BY has.profile_id
`` ), telegram_data AS (
`` SELECT wp_appq_custom_user_field_data.profile_id,
`` wp_appq_custom_user_field_data.value
`` FROM wp_appq_custom_user_field_data
`` WHERE wp_appq_custom_user_field_data.custom_user_field_id = 22 AND wp_appq_custom_user_field_data.value ≠ `''`::text
`` ), partita_iva_data AS (
`` SELECT wp_appq_custom_user_field_data.profile_id,
`` CASE wp_appq_custom_user_field_data.value
`` WHEN '578'::text THEN false
`` WHEN '579'::text THEN true
`` ELSE NULL::boolean
`` END AS has_partita_iva
`` FROM wp_appq_custom_user_field_data
`` WHERE wp_appq_custom_user_field_data.custom_user_field_id = 39
`` ), fiscal_data AS (
`` SELECT rk.tester_id,
`` rk.id
`` FROM ( SELECT wp_appq_fiscal_profile.tester_id,
`` wp_appq_fiscal_profile.id,
`` rank() OVER (PARTITION BY wp_appq_fiscal_profile.tester_id ORDER BY wp_appq_fiscal_profile.verified_on DESC) AS last_verified_rank
`` FROM wp_appq_fiscal_profile
`` WHERE wp_appq_fiscal_profile.is_active = 1) rk
`` WHERE rk.last_verified_rank = 1
`` ), booty_data AS (
`` SELECT wp_appq_payment.tester_id,
`` sum(wp_appq_payment.amount) AS total_booty_generated,
`` min(wp_appq_payment.creation_date) AS first_booty_creation_date,
`` max(wp_appq_payment.creation_date) AS last_booty_creation_date
`` FROM wp_appq_payment
`` GROUP BY wp_appq_payment.tester_id
`` ), payments_data AS (
`` SELECT
`` s.tester_id,
`` SUM(s.amount) as amount,
`` SUM(s.amount_gross) as amount_gross
`` FROM
`` wp_appq_payment_request s
`` WHERE
`` s.is_paid = 1
`` GROUP BY
`` s.tester_id
`` ), course_data AS (
`` SELECT c_row.tester_id,
`` max(c_row.level) FILTER (WHERE c_row.career = 'General'::text) AS general_lv,
`` max(c_row.level) FILTER (WHERE c_row.career = 'Functional'::text) AS functional_lv,
`` max(c_row.level) FILTER (WHERE c_row.career = 'UX'::text) AS ux_lv
`` FROM ( SELECT complete.tester_id,
`` course.career,
`` max(course.level) AS level
`` FROM wp_appq_course_tester_status complete
`` LEFT JOIN wp_appq_course course ON complete.course_id = course.id
`` WHERE complete.is_completed = 1
`` GROUP BY complete.tester_id, course.career) c_row
`` GROUP BY c_row.tester_id
`` )
``SELECT t.id,
`` t.wp_user_id,
`` fp.id AS fiscal_profile_id,
`` CASE
`` WHEN p.name = 'Deleted User'::text THEN 'Deleted'::text
`` ELSE 'Active'::text
`` END AS status,
`` CASE
`` WHEN p.is_special = 1 THEN 'Special'::text
`` WHEN p.blacklisted = 1 THEN 'Blacklisted'::text
`` ELSE NULL::text
`` END AS flag,
`` CASE p.is_verified
`` WHEN 1 THEN true
`` ELSE false
`` END AS email_verified,
`` p.creation_time AS subscription,
`` p.last_modified,
`` COALESCE(p.last_activity, p.deletion_date, p.creation_time) AS last_activity,
`` p.deletion_date,
`` CASE
`` WHEN p.name = 'Deleted User'::text THEN NULL::text
`` ELSE concat(p.surname, ' ', p.name)
`` END AS full_name,
`` CASE
`` WHEN p.name = 'Deleted User'::text THEN NULL::text
`` ELSE p.name
`` END AS name,
`` p.surname,
`` p.email,
`` p.phone_number,
`` CASE p.sex
`` WHEN 1 THEN 'Male'::text
`` WHEN 0 THEN 'Female'::text
`` WHEN 2 THEN 'Non-binary'::text
`` ELSE NULL::text
`` END AS sex,
`` p.birth_date,
`` date_part('year'::text, age(p.birth_date)) AS age,
`` p.country,
`` CASE
`` WHEN p.country = 'United States of America'::text THEN 'United States'::text
`` WHEN p.country = 'Tanzania, United Republic of'::text THEN 'Tanzania'::text
`` WHEN p.country = 'South Korea'::text THEN 'Korea'::text
`` WHEN p.country = 'Reunion'::text OR p.country = 'French Guiana'::text THEN 'France'::text
`` WHEN p.country = 'North Macedonia, Republic of'::text THEN 'Macedonia'::text
`` WHEN p.country = 'Ivory Coast'::text THEN 'Côte D`''`Ivoire'::text
`` WHEN p.country = 'Iran, Islamic Republic of'::text THEN 'Iran'::text
`` WHEN p.country = 'Bouvet Island'::text THEN 'Norway'::text
`` WHEN p.country = 'Gibraltar'::text THEN 'United Kingdom'::text
`` WHEN p.country = 'Moldova, Republic of'::text THEN 'Moldova'::text
`` ELSE p.country
`` END AS country_holistics_compliant_format,
`` p.city,
`` edu.display_name AS education,
`` emp.display_name AS employment,
`` emp.category AS employment_type,
`` lang_group.languages,
`` telegram.value AS telegram_username,
`` piva.has_partita_iva,
`` dev.devices AS enabled_devices,
`` exp_pts.total_experience_points,
`` CASE p.entry_test
`` WHEN 1 THEN true
`` ELSE false
`` END AS entry_test,
`` CASE COALESCE(exp_pts.valid_records_per_user, 0::bigint)
`` WHEN 0 THEN false
`` ELSE true
`` END AS community_member,
`` cml.metal_level_label,
`` COALESCE(courses.general_lv, 0) AS general_course_lv,
`` COALESCE(courses.functional_lv, 0) AS functional_course_lv,
`` COALESCE(courses.ux_lv, 0) AS ux_course_lv,
`` COALESCE(cnd.n_cp_candidate, 0::bigint) AS n_candidate_campaigns,
`` COALESCE(cnd.n_cp_accepted, 0::bigint) AS n_accepted_campaigns,
`` COALESCE(ccp.n_campaigns, 0::bigint) AS n_completed_campaigns,
`` COALESCE(ccp.n_functional_campaigns, 0::bigint) AS n_completed_functional_campaigns,
`` COALESCE(ccp.n_special_campaigns, 0::bigint) AS n_completed_special_campaigns,
`` COALESCE(ccp.n_ux_campaigns, 0::bigint) AS n_completed_ux_campaigns,
`` COALESCE(ccp.n_eth_campaigns, 0::bigint) AS n_completed_eth_campaigns,
`` fcp.first_campaign_id,
`` fcp.first_campaign,
`` fcp.first_campaign_start_date,
`` fcp.first_campaign_end_date,
`` fcp.first_campaign_close_date,
`` lcp.latest_campaign_id,
`` lcp.latest_campaign,
`` lcp.latest_campaign_start_date,
`` lcp.latest_campaign_end_date,
`` lcp.latest_campaign_close_date,
`` COALESCE(bug.n_bug_found, 0::bigint) AS n_total_bug_found,
`` COALESCE(b.total_booty_generated, 0::double precision) AS total_booty_generated,
`` b.first_booty_creation_date,
`` b.last_booty_creation_date,
`` COALESCE(pd.amount_gross, 0::double precision) AS total_cashed_payout --migrated from previous "amount"
`` FROM tryber_rows t
`` LEFT JOIN current_metal_levels cml ON t.id = cml.tester_id
`` LEFT JOIN wp_appq_evd_profile p ON t.id = p.id
`` LEFT JOIN wp_appq_education edu ON p.education_id = edu.id
`` LEFT JOIN wp_appq_employment emp ON p.employment_id = emp.id
`` LEFT JOIN device_data dev ON p.id = dev.profile_id
`` LEFT JOIN completed_campaign_data ccp ON p.id = ccp.profile_id
`` LEFT JOIN candidate_data cnd ON p.wp_user_id = cnd.user_id
`` LEFT JOIN first_campaign_data fcp ON p.id = fcp.profile_id
`` LEFT JOIN latest_campaign_data lcp ON p.id = lcp.profile_id
`` LEFT JOIN bug_data bug ON p.wp_user_id = bug.user_id
`` LEFT JOIN experience_points_data exp_pts ON p.id = exp_pts.tester_id
`` LEFT JOIN language_data lang_group ON p.id = lang_group.profile_id
`` LEFT JOIN telegram_data telegram ON p.id = telegram.profile_id
`` LEFT JOIN partita_iva_data piva ON p.id = piva.profile_id
`` LEFT JOIN fiscal_data fp ON p.id = fp.tester_id
`` LEFT JOIN booty_data b ON p.id = b.tester_id
`` LEFT JOIN payments_data pd ON pd.tester_id = p.id
`` LEFT JOIN course_data courses ON p.id = courses.tester_id;