Untitled

with salary_type as (SELECT jobid, salaryestimatessource, salaryestimatesperiod, salaryestimatesmincents, salaryestimatesmaxcents,
                            CASE
                             WHEN arrays_overlap(grpunion,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst0', '#A11:mosaic_provider_company_info_mobvj_salary_tst0', '#a10:mosaic_provider_company_info_mobvj_salary_tst0', '#A10:mosaic_provider_company_info_mobvj_salary_tst0'])
                                 THEN '0'
                             WHEN arrays_overlap(grpunion,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst4', '#A11:mosaic_provider_company_info_mobvj_salary_tst4', '#a10:mosaic_provider_company_info_mobvj_salary_tst4', '#A10:mosaic_provider_company_info_mobvj_salary_tst4'])
                                 THEN '4'
                             WHEN arrays_overlap(grpunion,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst5', '#A11:mosaic_provider_company_info_mobvj_salary_tst5'])
                                 THEN '5' END AS variant
                     FROM imhotep.coinfppageview
                     WHERE unixtime BETWEEN imhotep_unixtime('2021-11-18') AND imhotep_unixtime('2021-11-25')
    GROUP BY 1,2,3,4,5,6),
     behavior as (SELECT ctkrcvd,
                         jobid,
                         CASE
                             WHEN arrays_overlap(grp,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst0', '#A11:mosaic_provider_company_info_mobvj_salary_tst0', '#a10:mosaic_provider_company_info_mobvj_salary_tst0', '#A10:mosaic_provider_company_info_mobvj_salary_tst0'])
                                 THEN '0'
                             WHEN arrays_overlap(grp,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst4', '#A11:mosaic_provider_company_info_mobvj_salary_tst4', '#a10:mosaic_provider_company_info_mobvj_salary_tst4', '#A10:mosaic_provider_company_info_mobvj_salary_tst4'])
                                 THEN '4'
                             WHEN arrays_overlap(grp,
                                                 ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst5', '#A11:mosaic_provider_company_info_mobvj_salary_tst5'])
                                 THEN '5' END AS variant,
                         DATE_FORMAT(cast(day as timestamp), '%Y%m%d') as day,
                         sum(applystarts)     as applystarts
                  FROM mobviewjob
                  WHERE unixtime BETWEEN imhotep_unixtime('2021-11-18') AND imhotep_unixtime('2021-11-25')
                    AND contains(rcv, 'jsv')
                    AND contains(grp, 'smartphone')
                    AND not contains(grp, 'privileged')
                    AND arrays_overlap(grp,
                                       ARRAY ['#a11:mosaic_provider_company_info_mobvj_salary_tst0', '#A11:mosaic_provider_company_info_mobvj_salary_tst0', '#a10:mosaic_provider_company_info_mobvj_salary_tst0', '#A10:mosaic_provider_company_info_mobvj_salary_tst0',
                                           '#a11:mosaic_provider_company_info_mobvj_salary_tst4', '#A11:mosaic_provider_company_info_mobvj_salary_tst4', '#a10:mosaic_provider_company_info_mobvj_salary_tst4', '#A10:mosaic_provider_company_info_mobvj_salary_tst4',
                                           '#a11:mosaic_provider_company_info_mobvj_salary_tst5', '#A11:mosaic_provider_company_info_mobvj_salary_tst5'])
                  GROUP BY 1, 2, 3, 4),
     estimated_salary as (SELECT jobid, approx_percentile(estimatedsalary, 0.5) annual_estimated,
                                 approx_percentile(intsalarymin, 0.5) min_extracted,
                                 approx_percentile(intsalarymax, 0.5) max_extracted,
                                 max(salarytype) type_extracted
                          FROM searchablejobs
                          WHERE unixtime BETWEEN imhotep_unixtime('2021-11-18') AND imhotep_unixtime('2021-11-25') AND jobcountry='US'
                          GROUP BY 1)
SELECT behavior.day,
       ctkrcvd,
       behavior.jobid  as jobid,
       behavior.variant,
       applystarts,
       salaryestimatessource,
       salaryestimatesperiod,
       salaryestimatesmincents,
       salaryestimatesmaxcents,
       annual_estimated,
       min_extracted,
       max_extracted,
       type_extracted
FROM behavior
         LEFT JOIN salary_type ON behavior.jobid = salary_type.jobid AND behavior.variant = salary_type.variant
         LEFT JOIN estimated_salary ON behavior.jobid = estimated_salary.jobid