SQL每日一题（5）

前言：五更！五更琉璃！不对！是，五更佩可！

原始数据：

new_hires

题目一：

查询新进类型中，不同原因的合计人数以及人数排名。

结果输出原因、人数、排名。

题目二：

排名第 4 的原因是什么？对应人数为多少？

填写示例：社会招聘 20

题目一：思路：排名当然dense_rank排序了，然后其余直接查询就好

SELECT
    reason,
    COUNT(*) AS num_people,
    DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
FROM new_hires
GROUP BY reason
ORDER BY rk;

题目二：搜索对应rank =4的即可

WITH t1 AS (
    SELECT
        reason,
        COUNT(*) AS num_people,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rk
    FROM new_hires
    GROUP BY reason
)
SELECT
    reason,
    num_people
FROM t1
WHERE rk = 4;

或者利用order by desc limit offset即可

SELECT
    reason,
    COUNT(*) AS num_people
FROM new_hires
GROUP BY reason
ORDER BY num_people DESC  -- 降序排列，人数最多的在前
LIMIT 1 OFFSET 3;        -- 跳过前3名，取第4名