LeetCode 2346 - Compute the Rank as a Percentage

LeetCode Problem 2346

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

The problem provides a database table named Students, where each row represents a student and contains three fields:

• student_id, the unique identifier for the student
• department_id, the department the student belongs to
• mark, the student's exam score

The goal is to compute each student's rank percentage inside their department.

The ranking is based on descending marks. A student with a higher score receives a better rank, meaning rank 1 corresponds to the highest mark. If multiple students have the same mark, they share the same rank. This behavior matches the SQL RANK() window function, not DENSE_RANK().

After determining the rank, we compute the percentage using this formula:

$\frac{(\text{rank}-1)\times100}{(\text{department_size}-1)}$

The result must be rounded to two decimal places.

The output should contain:

• student_id
• department_id
• percentage

The order of rows does not matter.

A very important detail is that the denominator is (department_size - 1). This means we compare the student's position relative to the rest of the department. The best student always gets 0.0, and the worst student gets 100.0.

The problem guarantees that students belong to departments and that marks are integers. Since ties are allowed, multiple students can receive identical percentages.

One edge case is when all students in a department have the same score. In that situation, they all receive rank 1, so every percentage becomes 0.0.

Another important edge case is a department containing only one student. The formula would divide by zero because (1 - 1) = 0. In practice, SQL solutions handle this carefully using conditional logic such as CASE WHEN count = 1 THEN 0.

Approaches

Brute Force Approach

The brute force method compares every student with every other student inside the same department.

For each student:

1. Find all students in the same department.
2. Count how many students have a strictly higher mark.
3. The rank becomes:

higher_scores + 1

4. Count the total number of students in that department.
5. Apply the percentage formula.

This approach is correct because rank is defined entirely by relative ordering within the department. By directly counting higher scores, we reproduce the exact ranking definition.

However, this method is inefficient because each student potentially scans all other students in the department. If there are n students, the complexity becomes quadratic.

Optimal Approach

The key observation is that SQL window functions already provide ranking and partitioning capabilities efficiently.

We can use:

• RANK() OVER (PARTITION BY department_id ORDER BY mark DESC)
• COUNT(*) OVER (PARTITION BY department_id)

The RANK() function automatically handles ties correctly. Students with identical marks receive the same rank, and later ranks skip numbers appropriately.

The COUNT() window function gives the total number of students in the department without requiring separate joins or subqueries.

Once these two values are available, we simply compute the percentage formula and round the result.

This approach is efficient, concise, and maps directly to the problem definition.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n²)	O(1)	Compare every student against every other student in the department
Optimal	O(n log n)	O(n)	Uses SQL window functions for ranking and department counts

Algorithm Walkthrough

1. Partition the students by department_id.

This ensures rankings are computed independently for each department. Students from different departments should never affect each other's rank. 2. Sort students inside each department by mark in descending order.

Higher marks correspond to better ranks, so descending order is required. 3. Compute the rank using RANK().

Students with identical marks receive the same rank. If two students tie for rank 1, the next student receives rank 3. 4. Compute the total number of students in each department using COUNT(*).

This value is required for the denominator of the percentage formula. 5. Handle the single student department edge case.

If a department contains only one student, (count - 1) becomes zero. In this case, the percentage should be 0.0. 6. Apply the percentage formula.

Compute:

$\frac{(rank-1)\times100}{(count-1)}$ 7. Round the result to two decimal places.

SQL's ROUND() function ensures the required formatting.

Why it works

The algorithm works because RANK() exactly matches the problem's ranking definition. Partitioning by department guarantees independent rankings, and sorting by descending marks ensures higher scores receive smaller ranks. The percentage formula directly maps the rank position into a normalized range between 0 and 100.

Python Solution

# Write your MySQL query statement below

SELECT
    student_id,
    department_id,
    ROUND(
        CASE
            WHEN department_count = 1 THEN 0
            ELSE (student_rank - 1) * 100.0 / (department_count - 1)
        END,
        2
    ) AS percentage
FROM (
    SELECT
        student_id,
        department_id,
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY mark DESC
        ) AS student_rank,
        COUNT(*) OVER (
            PARTITION BY department_id
        ) AS department_count
    FROM Students
) ranked_students;

The solution first creates a derived table named ranked_students.

Inside this subquery:

• RANK() computes the student's position within their department.
• COUNT(*) computes the total number of students in that department.

The outer query applies the percentage formula.

The CASE statement prevents division by zero for departments containing only one student.

Finally, ROUND(..., 2) formats the result to two decimal places exactly as required.

Go Solution

// There is no Go solution for this problem because
// LeetCode 2346 is a Database problem solved using SQL.

Since this is a database problem, LeetCode expects an SQL query rather than executable Go code. Unlike algorithmic coding problems, there is no function signature or runtime environment for Go.

Worked Examples

Example 1

Input table:

student_id	department_id	mark
2	2	650
8	2	650
7	1	920
1	1	610
3	1	530

Department 1 Processing

Students:

student_id	mark
7	920
1	610
3	530

After sorting descending:

student_id	mark	rank
7	920	1
1	610	2
3	530	3

Department size is 3.

Now compute percentages:

student_id	formula	percentage
7	(1 - 1) × 100 / (3 - 1)	0.0
1	(2 - 1) × 100 / (3 - 1)	50.0
3	(3 - 1) × 100 / (3 - 1)	100.0

Department 2 Processing

Students:

student_id	mark
2	650
8	650

Both students share the same mark, so both receive rank 1.

student_id	mark	rank
2	650	1
8	650	1

Department size is 2.

Percentages:

student_id	formula	percentage
2	(1 - 1) × 100 / (2 - 1)	0.0
8	(1 - 1) × 100 / (2 - 1)	0.0

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n log n)	Ranking requires sorting students within departments
Space	O(n)	Window function processing stores ranking metadata

The dominant cost comes from ordering rows within each department for the ranking operation. Window functions internally require sorting based on the partition and ordering columns. The additional memory usage comes from storing intermediate ranking and counting information during query execution.

Test Cases

# Example from problem statement
# Department 1 produces 0, 50, 100 percentages
# Department 2 has tied top scores
students = [
    (2, 2, 650),
    (8, 2, 650),
    (7, 1, 920),
    (1, 1, 610),
    (3, 1, 530),
]

# Single student department
# Should safely return 0.0 instead of dividing by zero
students = [
    (1, 1, 700),
]

# All students tied
# Every student should receive rank 1 and percentage 0.0
students = [
    (1, 1, 500),
    (2, 1, 500),
    (3, 1, 500),
]

# Strictly descending marks
# Percentages should spread evenly
students = [
    (1, 1, 100),
    (2, 1, 90),
    (3, 1, 80),
    (4, 1, 70),
]

# Multiple departments with independent rankings
students = [
    (1, 1, 95),
    (2, 1, 90),
    (3, 2, 100),
    (4, 2, 50),
]

# Tie in middle ranks
students = [
    (1, 1, 100),
    (2, 1, 90),
    (3, 1, 90),
    (4, 1, 80),
]

Test	Why
Problem example	Validates normal ranking and ties
Single student department	Verifies division by zero handling
All students tied	Ensures shared rank behavior works
Strict descending marks	Confirms percentage distribution
Multiple departments	Ensures partitions are independent
Tie in middle ranks	Verifies skipped ranks from RANK()

Edge Cases

Department With Only One Student

A department containing a single student is the most important edge case because the denominator becomes zero:

$count-1=0$

Without special handling, the query would raise a division by zero error. The implementation prevents this using a CASE statement that directly returns 0.

Multiple Students Sharing the Same Highest Mark

When several students tie for the top score, they must all receive rank 1. This means every tied student should receive percentage 0.0.

A common mistake is using ROW_NUMBER() instead of RANK(). ROW_NUMBER() would incorrectly assign distinct ranks to tied students. Using RANK() ensures the behavior matches the problem statement exactly.

Ties That Skip Rank Numbers

Suppose marks are:

mark
100
90
90
80

The ranks become:

mark	rank
100	1
90	2
90	2
80	4

Notice rank 3 is skipped. This is correct behavior for RANK(). A solution using DENSE_RANK() would incorrectly assign the last student rank 3, producing the wrong percentage.