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

with 
    month_info as(
        select
            job,date,num,
            substr(date,1,7) as mon
        from resume_info),
    t1 as(
        select
            job,
            mon,
            sum(num) as cnt
        from month_info
        where year(date)=2025
        group by job,mon),
    t2 as(
        select
            job,
            mon,
            sum(num) as cnt
        from month_info
        where year(date)=2026
        group by job,mon)

select
    t1.job as job,
    t1.mon as first_year_mon,
    t1.cnt as first_year_cnt,
    t2.mon as second_year_mon,
    t2.cnt as second_year_cnt
from t1 left join t2
on t1.job=t2.job 
	and substr(t1.mon,6,2)=substr(t2.mon,6,2)
order by t1.mon desc,t1.job desc