-- 链接 LINK: https://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c?tpId=82&rp=1&ru=%2Fexam%2Foj&qru=%2Fexam%2Foj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=&tags=&title=&gioEnter=menu
-- 标题 TITLE: SQL280 实习广场投递简历分析(三)
-- 日期 DATE: 2023-02-25

/* 设计思路
    解法加大了点难度，增加补全 12 月份的信息，以及考虑 2025 年和 2026 年岗位存在差异等情况
**/

-- 题解 SQL

-- 12 个月份临时表
WITH RECURSIVE monthly AS (
  SELECT 12 AS months
  UNION
  SELECT months-1 AS months FROM monthly WHERE months > 1
)

SELECT IFNULL(T2.job, T3.job) AS job, 
        IFNULL(T2.date, CONCAT('2025-', T1.months)) AS first_year_mon, 
        IFNULL(T2.cnt, 0) AS first_year_cnt,
        IFNULL(T3.date, CONCAT('2026-', T1.months)) AS second_year_mon, 
        IFNULL(T3.cnt, 0) AS second_year_cnt
FROM monthly T1
LEFT JOIN (
    -- 2025 年每月各职位投递份数，存在部分月份缺失
    SELECT DISTINCT job, DATE_FORMAT(date, '%Y-%m') AS date, MONTH(date) AS months,
        SUM(num) OVER(PARTITION BY job, DATE_FORMAT(date, '%Y-%m')) AS cnt
       FROM resume_info
    WHERE YEAR(date) = 2025 
) T2 ON T2.months = T1.months
LEFT JOIN (
    -- 2026 年每月各职位投递份数，存在部分月份缺失
    SELECT DISTINCT job, DATE_FORMAT(date, '%Y-%m') AS date, MONTH(date) AS months,
        SUM(num) OVER(PARTITION BY job, DATE_FORMAT(date, '%Y-%m')) AS cnt
       FROM resume_info
    WHERE YEAR(date) = 2026
) T3 ON T3.months = T1.months
WHERE (T2.job = T3.job AND T2.job IS NOT NULL) OR NOT (T2.job IS NULL AND T3.job IS NULL)
ORDER BY first_year_mon DESC, job DESC

;

-- 数据 DATA ===================================================
drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'C++','2026-01-04',230),
(14,'Java','2026-01-04',764),
(15,'Python','2026-01-04',644),
(16,'C++','2026-01-06',240),
(17,'Java','2026-01-06',714),
(18,'Python','2026-01-06',624),
(19,'C++','2026-02-14',260),
(20,'Java','2026-02-14',721),
(21,'Python','2026-02-14',321),
(22,'C++','2026-02-24',134),
(23,'Java','2026-02-24',928),
(24,'Python','2026-02-24',525),
(25,'C++','2027-02-06',231);