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

with
t1 as(
    select
        job,
        date_format(date,'%Y-%m') as first_year_mon,
        sum(num) as first_year_cnt
    from
        resume_info
    where
        date_format(date,'%Y')=2025
    group by
        job,
        date_format(date,'%Y-%m')
    order by
        job desc,
        first_year_mon desc
),
t2 as(
    select
        job,
        date_format(date,'%Y-%m') as second_year_mon,
        sum(num) as second_year_cnt
    from
        resume_info
    where
        date_format(date,'%Y')=2026
    group by
        job,
        date_format(date,'%Y-%m')
    order by
        job desc,
        second_year_mon desc
),
t3 as(
    select
        t1.job,
        first_year_mon,
        first_year_cnt,
        second_year_mon,
        second_year_cnt
    from
        t1 left join t2 using(job)
    where
        substring_index(first_year_mon,'-',-1)=substring_index(second_year_mon,'-',-1)
)

select * from t3 order by first_year_mon desc, job desc