题解 | 实习广场投递简历分析(三)

##这个哪里困难了

with tmp1 as  (
select job, substr(date,1,4) as y, substr(date,1,7) as ym , substr(date,6,2) as mon,sum(num) as num
from resume_info
where substr(date,1,4)  in ('2025','2026')
group by job, substr(date,1,4),  substr(date,1,7)  , substr(date,6,2) 
) 
select t1.job , t1.ym as first_year_mon , t1.num as first_year_cnt 
                , t2.ym as second_year_mon , t2.num as second_year_cnt
from 
(select * from  tmp1 where y ='2025') t1 join 
(select * from  tmp1 where y ='2026') t2 on t1.job= t2.job and t1.mon =t2.mon
order by 2 desc ,1 desc