Department Exam Percentile Ranking

Medium

An academic analytics platform tracks exam marks for students across departments. Program heads want a normalized way to compare each student against peers in the same department. Table: Students student_id: unique student identifier department_id: department identifier mark: exam score Task: For every student row, compute a department-relative rank percentage using: percentage = (rank - 1) * 100 / (department_size - 1) Ranking behavior: Rank marks in descending order inside each department. Highest mark gets rank 1. Tied marks share the same rank (competition ranking). Edge behavior: If a department has only one student, that student's percentage is 0.00. Output requirements: Return exactly: student_id, department_id, percentage Round percentage to 2 decimal places Output order is not important Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 101        | 10            | 95   |
| 102        | 10            | 88   |
| 103        | 10            | 70   |
| 201        | 20            | 90   |
| 202        | 20            | 90   |

Output

[
  {"student_id":101,"department_id":10,"percentage":0.0},
  {"student_id":102,"department_id":10,"percentage":50.0},
  {"student_id":103,"department_id":10,"percentage":100.0},
  {"student_id":201,"department_id":20,"percentage":0.0},
  {"student_id":202,"department_id":20,"percentage":0.0}
]

Explanation

Department 10 has three students with ranks 1, 2, 3 mapping to 0, 50, and 100. Department 20 has a tie at the top, so both students receive rank 1 and percentage 0.

Example 2

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 301        | 30            | 77   |

Output

[
  {"student_id":301,"department_id":30,"percentage":0.0}
]

Explanation

A single-student department always yields 0.00 because the denominator department_size - 1 is zero.

Example 3

Input

Students:
| student_id | department_id | mark |
|------------|---------------|------|
| 401        | 40            | 92   |
| 402        | 40            | 85   |
| 403        | 40            | 85   |
| 404        | 40            | 60   |

Output

[
  {"student_id":401,"department_id":40,"percentage":0.0},
  {"student_id":402,"department_id":40,"percentage":33.33},
  {"student_id":403,"department_id":40,"percentage":33.33},
  {"student_id":404,"department_id":40,"percentage":100.0}
]

Explanation

Marks 85 and 85 share rank 2. With four students, percentage for rank 2 is (2 - 1) * 100 / 3 = 33.33.

Constraints

0 <= number of rows in Students <= 200000
student_id values are unique
1 <= student_id <= 2000000000
1 <= department_id <= 2000000000
0 <= mark <= 100000
Ranking uses descending mark order per department
Equal marks must share rank (RANK semantics)
If department size is 1, percentage must be 0.00
Round percentage to exactly 2 decimal places
Return columns exactly as: student_id, department_id, percentage
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: student_id, department_id, percentage

Students: | student_id | department_id | mark | |------------|---------------|------| | 101 | 10 | 95 | | 102 | 10 | 88 | | 103 | 10 | 70 | | 201 | 20 | 90 | | 202 | 20 | 90 |

[ {"student_id":101,"department_id":10,"percentage":0.0}, {"student_id":102,"department_id":10,"percentage":50.0}, {"student_id":103,"department_id":10,"percentage":100.0}, {"student_id":201,"department_id":20,"percentage":0.0}, {"student_id":202,"department_id":20,"percentage":0.0} ]

Students: | student_id | department_id | mark | |------------|---------------|------| | 401 | 40 | 92 | | 402 | 40 | 85 | | 403 | 40 | 85 | | 404 | 40 | 60 |

[ {"student_id":401,"department_id":40,"percentage":0.0}, {"student_id":402,"department_id":40,"percentage":33.33}, {"student_id":403,"department_id":40,"percentage":33.33}, {"student_id":404,"department_id":40,"percentage":100.0} ]