Untitled
unknown
plain_text
4 years ago
4.8 kB
5
Indexable
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
Editor is loading...