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

with
    tmp as (
        select distinct
            job,
            date_format (date, '%Y-%m') as d,
            sum(num) over (
                partition by
                    date_format (date, '%Y-%m'),
                    job
            ) as cnt,
            dense_rank() over (
                partition by
                    job
                order by
                    date_format (date, '%m') DESC
            ) as tk
        from
            resume_info
        where
            date < '2027-01-01'
    )
select distinct
    t1.job,
    t1.d as first_year_mon,
    t1.cnt as first_year_cnt,
    t2.d as second_year_mon,
    t2.cnt as second_year_cnt
from
    tmp as t1
    join tmp as t2 on t1.job = t2.job
    and t1.tk = t2.tk
    and timediff (t1.d, t2.d) = -1
order by
    first_year_mon DESC,
    job DESC