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
Compute Rank as Percentage INPUT: Students Table ID Dept Mark 1 A 90 2 A 85 3 A 80 4 B 95 5 B 70 Columns: - student_id (PK) - department_id - mark (score) Dept A: 3 students Dept B: 2 students ALGORITHM STEPS 1 Partition by Dept Group students by department_id 2 Rank by Mark DESC Order by mark descending within each partition 3 Count Students COUNT(*) OVER partition for each department 4 Apply Formula Compute percentage: (rank - 1) * 100 (count - 1) ROUND to 2 decimal places FINAL RESULT ID Dept Mark Pct% 1 A 90 0.00 2 A 85 50.00 3 A 80 100.00 4 B 95 0.00 5 B 70 100.00 Interpretation: 0% = Top rank (best) 100% = Bottom rank 50% = Middle position OK Key Insight: PERCENT_RANK() Window Function Use PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) for optimal solution. This built-in function automatically computes (rank-1)/(count-1) within each partition efficiently. TutorialsPoint - Compute the Rank as a Percentage | Optimal Solution

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