繼續做題！

原始表：employee_resignations表

employee_id	resignation_date
1001	2022-03-15
1002	2022-11-20
1003	2023-01-05
1004	2023-07-12
1005	2024-02-28

第一題：
查詢累計到每個年度的離職人數
結果輸出：年度、當年離職人數、累計離職人數

第二題：
第一次累計超過 100 人離職的是哪一年？對應的當年離職人數為多少？
填寫示例：2008 120

?第一題思路：累積離職利用sum窗口函數求和即可，當年離職利用order by排序即可

SELECTYEAR(resignation_date) AS year,COUNT(*) AS current_year_resignations,SUM(COUNT(*)) OVER (ORDER BY YEAR(resignation_date)) AS cumulative_resignations
FROM employee_resignations
GROUP BY YEAR(resignation_date)
ORDER BY YEAR(resignation_date);

第二題思路，將第一題轉換為t1表，在t1表基礎上進行查詢即可

WITH t1 AS (SELECT YEAR(resignation_date) AS year,COUNT(*) AS current_year_resignations,SUM(COUNT(*)) OVER (ORDER BY YEAR(resignation_date)) AS cumulative_resignationsFROM employee_resignationsGROUP BY YEAR(resignation_date)
)
SELECT year,current_year_resignations
FROM t1
WHERE cumulative_resignations >= 100
ORDER BY cumulative_resignations DESC
LIMIT 1;