Compute the Rank as a Percentage - Problem

You are given a Students table that contains information about students' exam marks in different departments.

Write a SQL solution to compute the rank of each student in their department as a percentage using this formula:

(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)

Requirements:

  • Student rank is determined by descending mark (highest mark = rank 1)
  • Students with the same mark get the same rank
  • Round the percentage to 2 decimal places
  • Return results in any order

Table Schema

Students
Column Name Type Description
student_id PK int Unique identifier for each student
department_id int ID of the department the student is enrolled in
mark int Student's exam mark
Primary Key: student_id
Note: Each student belongs to exactly one department and has one exam mark

Input & Output

Example 1 — Basic Department Ranking
Input Table:
student_id department_id mark
1 1 90
2 1 80
3 1 85
4 2 95
5 2 75
Output:
student_id department_id mark percentage
1 1 90 0.00
2 1 80 100.00
3 1 85 50.00
4 2 95 0.00
5 2 75 100.00
💡 Note:

In department 1: Student 1 (mark=90) ranks 1st → (1-1)*100/(3-1) = 0.00%. Student 3 (mark=85) ranks 2nd → (2-1)*100/(3-1) = 50.00%. Student 2 (mark=80) ranks 3rd → (3-1)*100/(3-1) = 100.00%. In department 2: Student 4 ranks 1st → 0.00%, Student 5 ranks 2nd → 100.00%.
Example 2 — Single Student Department
Input Table:
student_id department_id mark
1 1 85
2 2 90
Output:
student_id department_id mark percentage
1 1 85 0.00
2 2 90 0.00
💡 Note:

When a department has only one student, the percentage is always 0.00% since there's no one else to compare with. The formula would involve division by zero (1-1=0), so we handle this edge case explicitly.

Constraints

  • 1 ≤ student_id ≤ 10^5
  • 1 ≤ department_id ≤ 1000
  • 0 ≤ mark ≤ 100
  • Each student_id is unique

Visualization

Tap to expand
Rank as Percentage Problem OverviewStudents Tablestudent_iddept_idmark119021803185Department 1: 3 studentsRANK() +PercentageResult with Percentagesstudent_iddept_idmarkpercentage11900.002180100.00318550.00Rank 1→0%, Rank 2→50%, Rank 3→100%Formula: (rank - 1) × 100 ÷ (total - 1)Student 1: (1-1)×100÷(3-1) = 0.00%Student 3: (2-1)×100÷(3-1) = 50.00%Student 2: (3-1)×100÷(3-1) = 100.00%
Understanding the Visualization
1
Input
Students with departments and marks
2
Rank
RANK() within each department
3
Percentage
Apply formula and round
Key Takeaway
🎯 Key Insight: Use window functions partitioned by department to rank students and calculate percentages in a single efficient query

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen