Problem

Table: Students

1
2
3
4
5
6
7
8
9

+---------------+------+
| 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.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Input: 
Students table:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+
Output: 
+------------+---------------+------------+
| 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        |
+------------+---------------+------------+
Explanation: 
For Department 1:
- Student 7: percentage = (1 - 1) * 100 / (3 - 1) = 0.0
- Student 1: percentage = (2 - 1) * 100 / (3 - 1) = 50.0
- Student 3: percentage = (3 - 1) * 100 / (3 - 1) = 100.0
For Department 2:
- Student 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
- Student 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0

Solution

Method 1 – Window Functions and Ranking

Intuition

To compute the rank as a percentage for each student within their department, we need to:

  • Rank students by their marks (descending) within each department, handling ties.
  • Count the number of students in each department.
  • Apply the formula for percentage rank, rounding to two decimal places.

Approach

  1. Use a window function (DENSE_RANK or RANK) to assign ranks to students within each department, ordered by mark descending.
  2. Use COUNT(*) OVER (PARTITION BY department_id) to get the total number of students in each department.
  3. Compute the percentage using the formula: (student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1).
  4. Round the result to two decimal places. If a department has only one student, the percentage should be 0.00.

Code

1
2
3
4
5
6
7
8

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 ORDER BY 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,
  CASE WHEN cnt = 1 THEN 0.00
       ELSE ROUND((rnk - 1) * 100.0 / (cnt - 1), 2)
  END AS percentage
FROM (
  SELECT student_id, department_id, mark,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY 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
11
12

import pandas as pd

def compute_rank_percentage(students: pd.DataFrame) -> pd.DataFrame:
    students = students.copy()
    students['rnk'] = students.groupby('department_id')['mark'].rank(method='dense', ascending=False)
    students['cnt'] = students.groupby('department_id')['student_id'].transform('count')
    def pct(row):
        if row['cnt'] == 1:
            return 0.00
        return round((row['rnk'] - 1) * 100 / (row['cnt'] - 1), 2)
    students['percentage'] = students.apply(pct, axis=1)
    return students[['student_id', 'department_id', 'percentage']]

Complexity

  • ⏰ Time complexity: O(n)
  • 🧺 Space complexity: O(n)