+---------------+------+
| Column Name | Type |
+---------------+------+
| student_id | int |
| department_id | int |
| mark | int |
+---------------+------+
student_id contains unique values.
Each row of this table indicates a student's ID, the ID of the department in which the student enrolled, and their mark in the exam.
Write a solution to report the rank of each student in their department as a percentage, where the rank as a percentage is computed using the following formula: (student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1). The percentage should be
rounded to 2 decimal places. student_rank_in_the_department is determined by descending****mark, such that the student with the highest
mark is rank 1. If two students get the same mark, they also get the same rank.
SELECT student_id, department_id,
IF(cnt =1, 0.00, ROUND((rnk -1) *100/ (cnt -1), 2)) AS percentage
FROM (
SELECT student_id, department_id, mark,
DENSE_RANK() OVER (PARTITION BY department_id ORDERBY mark DESC) AS rnk,
COUNT(*) OVER (PARTITION BY department_id) AS cnt
FROM Students
) t;
1
2
3
4
5
6
7
8
9
10
SELECT student_id, department_id,
CASEWHEN cnt =1THEN0.00ELSE ROUND((rnk -1) *100.0/ (cnt -1), 2)
ENDAS percentage
FROM (
SELECT student_id, department_id, mark,
DENSE_RANK() OVER (PARTITION BY department_id ORDERBY mark DESC) AS rnk,
COUNT(*) OVER (PARTITION BY department_id) AS cnt
FROM Students
) t;