Compute the Rank as a Percentage - Practice Coding Problems

Compute the Rank as a Percentage - Problem

Database Medium

Compute Student Rank as Percentage

Imagine you're a university administrator tasked with creating a percentile ranking system for students within their departments. You need to rank each student based on their exam performance and convert that rank into a meaningful percentage score.

Given a Students table with student information including their department and exam marks, your goal is to:

📊 Calculate each student's rank within their department (highest mark = rank 1)
📈 Convert the rank to a percentage using the formula: (rank - 1) × 100 / (total_students_in_dept - 1)
🎯 Handle ties properly - students with identical marks get the same rank
🔢 Round to 2 decimal places for clean presentation

The percentage formula creates a scale where the top student gets 0% (representing the best performance) and the bottom student gets 100% (representing the worst performance relative to peers).

Table Structure:
Students: student_id (int), department_id (int), mark (int)

Input & Output

example_1.sql — Basic Case

$ Input: Students table: | student_id | department_id | mark | |------------|---------------|------| | 1 | 1 | 85 | | 2 | 1 | 95 | | 3 | 1 | 80 | | 4 | 2 | 92 | | 5 | 2 | 78 |

› Output: | student_id | department_id | mark | percentage | |------------|---------------|------|------------| | 1 | 1 | 85 | 50.00 | | 2 | 1 | 95 | 0.00 | | 3 | 1 | 80 | 100.00 | | 4 | 2 | 92 | 0.00 | | 5 | 2 | 78 | 100.00 |

💡 Note: In department 1: Student 2 (95) ranks 1st → 0%, Student 1 (85) ranks 2nd → 50%, Student 3 (80) ranks 3rd → 100%. In department 2: Student 4 (92) ranks 1st → 0%, Student 5 (78) ranks 2nd → 100%.

example_2.sql — Tied Scores

$ Input: Students table: | student_id | department_id | mark | |------------|---------------|------| | 1 | 1 | 85 | | 2 | 1 | 95 | | 3 | 1 | 85 | | 4 | 1 | 90 |

› Output: | student_id | department_id | mark | percentage | |------------|---------------|------|------------| | 1 | 1 | 85 | 66.67 | | 2 | 1 | 95 | 0.00 | | 3 | 1 | 85 | 66.67 | | 4 | 1 | 90 | 33.33 |

💡 Note: Students 1 and 3 both scored 85 and tie for rank 3. Using formula: rank 1 → 0%, rank 2 → 33.33%, tied rank 3 → 66.67%. Total students = 4, so denominator is 3.

example_3.sql — Single Student Department

$ Input: Students table: | student_id | department_id | mark | |------------|---------------|------| | 1 | 1 | 85 | | 2 | 2 | 95 |

› Output: | student_id | department_id | mark | percentage | |------------|---------------|------|------------| | 1 | 1 | 85 | 0.00 | | 2 | 2 | 95 | 0.00 |

💡 Note: When a department has only one student, the percentage is always 0.00 since there's no comparison possible (division by zero avoided with CASE statement).

Constraints

1 ≤ student_id ≤ 10⁵
1 ≤ department_id ≤ 10³
0 ≤ mark ≤ 100
Each student_id is unique
At least 1 student per department in test cases

Visualization

Tap to expand

Understanding the Visualization

Group by Department

Partition students into their respective departments

Rank by Performance

Sort students by marks in descending order within each department

Handle Ties

Students with identical marks receive the same rank

Calculate Percentile

Convert rank to percentage using (rank-1)*100/(total-1) formula

Special Cases

Single-student departments get 0% (no competition)

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY enable efficient per-department ranking in O(n log n) time, while the percentage formula creates a meaningful 0-100% scale where 0% represents the best performance.

Asked in

G Google 12 a Amazon 8 ⊞ Microsoft 6 f Meta 4

The optimal solution uses SQL window functions with RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) to efficiently rank students within their departments, then applies the percentage formula (rank-1)*100/(total-1) with proper handling for single-student departments using CASE statements.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use SQL window functions to rank students efficiently in a single pass
Brute Force (Subqueries)	O(n²)	O(1)	Use nested subqueries to count students and calculate ranks manually

Window Functions (Optimal) — Algorithm Steps

Use RANK() OVER (PARTITION BY department_id ORDER BY mark DESC) to get ranks
Use COUNT(*) OVER (PARTITION BY department_id) to get department sizes
Apply the percentage formula with proper case handling for single-student departments
Round the result to 2 decimal places using ROUND function

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition Data

Group students by department_id

Rank Within Groups

Use RANK() to order by marks DESC within each department

Count Group Size

COUNT(*) OVER gets department size

Calculate Percentage

Apply formula (rank-1)*100/(count-1)

Code -

solution.c — C

// Optimal SQL solution using window functions
char* sqlQuery = "\
SELECT \
    student_id, \
    department_id, \
    mark, \
    ROUND( \
        CASE \
            WHEN dept_count = 1 THEN 0.00 \
            ELSE (student_rank - 1) * 100.0 / (dept_count - 1) \
        END, 2 \
    ) AS percentage \
FROM ( \
    SELECT \
        student_id, \
        department_id, \
        mark, \
        RANK() OVER ( \
            PARTITION BY department_id \
            ORDER BY mark DESC \
        ) AS student_rank, \
        COUNT(*) OVER ( \
            PARTITION BY department_id \
        ) AS dept_count \
    FROM Students \
) ranked_students \
ORDER BY student_id";

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting for ranking within each department

⚡ Linearithmic

Space Complexity

O(n)

Space for sorting and window function intermediate results

⚡ Linearithmic Space

21.4K Views

Medium Frequency

~15 min Avg. Time

892 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler