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

with 2025_resume as (
    select
        job,
        substr(date,1,7) as first_year_mon,
        sum(num) as first_year_cnt
    from 
        resume_info
    where 
        substr(date,1,4) = 2025
    group by 
        job,
        substr(date,1,7)
),

2026_resume as (
    select
        job,
        substr(date,1,7) as second_year_mon,
        sum(num) as second_year_cnt
    from 
        resume_info
    where 
        substr(date,1,4) = 2026
    group by 
        job,
        substr(date,1,7)
)

select
    2025_resume.job,
    2025_resume.first_year_mon,
    2025_resume.first_year_cnt,
    2026_resume.second_year_mon,
    2026_resume.second_year_cnt
from 
    2025_resume
join 
    2026_resume
on 
    2025_resume.job = 2026_resume.job
and
    substr(2025_resume.first_year_mon,6,2) = substr(2026_resume.second_year_mon,6,2)
order by 
    2025_resume.first_year_mon desc,
    2025_resume.job desc