单挑力扣（LeetCode）SQL笔试题：2346. 以百分比计算排名（难度：中等）

表: Students
+---------------+------+
| Column Name   | Type |
+---------------+------+
| student_id    | int  |
| department_id | int  |
| mark          | int  |
+---------------+------+
student_id 是该表的主键。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。


编写一个 SQL 查询，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:
(student_rank_in_the_department - 1) * 100  (the_number_of_students_in_the_department - 1)。percentage 应该四舍五入到小数点后两位。
student_rank_in_the_department由mark的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。
以 任意顺序 返回结果表。


查询结果格式如下所示。
示例 1:
输入: 
Students 表:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+
输出: 
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7          | 1             | 0.0        |
| 1          | 1             | 50.0       |
| 3          | 1             | 100.0      |
| 2          | 2             | 0.0        |
| 8          | 2             | 0.0        |
+------------+---------------+------------+
解释: 
对于院系 1:
 - 学生 7:percentage = (1 - 1)* 100  (3 - 1) = 0.0
 - 学生 1:percentage = (2 - 1)* 100  (3 - 1) = 50.0
 - 学生 3:percentage = (3 - 1)* 100  (3 - 1) = 100.0
对于院系 2:
 - 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
 - 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0


来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/compute-the-rank-as-a-percentage

#测试数据
Create table If Not Exists Students (student_id int, department_id int, mark int);


insert into Students (student_id, department_id, mark) values ('2', '2', '650');
insert into Students (student_id, department_id, mark) values ('8', '2', '650');
insert into Students (student_id, department_id, mark) values ('7', '1', '920');
insert into Students (student_id, department_id, mark) values ('1', '1', '610');
insert into Students (student_id, department_id, mark) values ('3', '1', '530');

with
tmp1 as (
    select
        student_id,
        rank() over(partition by department_id order by mark desc) rk
    from Students
),
tmp2 as (
    select
        department_id,
        count(1) cnt
    from Students
    group by department_id
)
select
    a.student_id,
    a.department_id,
    case when c.cnt = 1 then 0.00 else round((b.rk-1)*100/(c.cnt-1),2) end percentage
from Students a
inner join tmp1 b
on a.student_id = b.student_id
inner join tmp2 c
on a.department_id = c.department_id;