Skip to main content
Talentd

Compute the Rank as a Percentage - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Students

+---------------+------+
| Column Name   | Type |
+---------------+------+
| student_id    | int  |
| department_id | int  |
| mark          | int  |
+---------------+------+
student_id contains unique values.
Each row of this table indicates a student's ID, the ID of the department in which the student enrolled, and their mark in the exam.

 

Write a solution to report the rank of each student in their department as a percentage, where the rank as a percentage is computed using the following formula: (student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1). The percentage should be rounded to 2 decimal places. student_rank_in_the_department is determined by descending mark, such that the student with the highest mark is rank 1. If two students get the same mark, they also get the same rank.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Students table:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+
Output: 
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7          | 1             | 0.0        |
| 1          | 1             | 50.0       |
| 3          | 1             | 100.0      |
| 2          | 2             | 0.0        |
| 8          | 2             | 0.0        |
+------------+---------------+------------+
Explanation: 
For Department 1:
 - Student 7: percentage = (1 - 1) * 100 / (3 - 1) = 0.0
 - Student 1: percentage = (2 - 1) * 100 / (3 - 1) = 50.0
 - Student 3: percentage = (3 - 1) * 100 / (3 - 1) = 100.0
For Department 2:
 - Student 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
 - Student 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0

Approach Overview

Problem Overview: Given a table of scores, compute each score's rank expressed as a percentage relative to the entire dataset. The percentage reflects how a value ranks compared to other rows when ordered by score.

Approach 1: PERCENT_RANK Window Function (O(n log n) time, O(1) extra space)

The cleanest solution uses the SQL window function PERCENT_RANK(). The database sorts rows by score and computes the percentage rank automatically using the formula (rank - 1) / (total_rows - 1). You simply apply PERCENT_RANK() OVER (ORDER BY score) to each row. Internally, the database performs a sort on the score column, which dominates the cost. This approach is concise, readable, and exactly matches how ranking percentages are defined in analytical SQL workloads.

Approach 2: Manual Rank Formula with Window Functions (O(n log n) time, O(1) extra space)

If PERCENT_RANK() is unavailable, compute the percentage manually using RANK() and COUNT(). First calculate the rank of each score using RANK() OVER (ORDER BY score). Then divide (rank - 1) by (total_rows - 1), where total_rows is obtained from COUNT(*) OVER(). This produces the same output as PERCENT_RANK. The database still performs a sort for ranking, so time complexity remains O(n log n). This approach demonstrates understanding of how ranking metrics are derived.

Both solutions rely on SQL database query techniques and analytical window functions. The key operation is ordering the dataset and computing a relative position for each row.

Recommended for interviews: Use the PERCENT_RANK() window function when the database supports it. It communicates intent clearly and avoids unnecessary arithmetic. If the interviewer wants to test deeper SQL knowledge, derive the value manually with RANK() and COUNT(). Showing both approaches proves you understand how ranking metrics are calculated internally.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
PERCENT_RANK Window FunctionO(n log n)O(1)Best option when the SQL engine supports PERCENT_RANK. Clean and concise.
Manual Formula with RANK() and COUNT()O(n log n)O(1)When PERCENT_RANK is unavailable or when explaining the ranking math in interviews.

Video Solution

Leetcode MEDIUM 2346 - Compute RANK as Percentage - RANK & COUNT WINDOW Functions - Explained by EDS • Everyday Data Science • 489 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Compute the Rank as a Percentage easy or hard?
Compute the Rank as a Percentage is considered a Medium-level SQL problem. The challenge comes from understanding analytical window functions and the formula behind percentage ranking rather than complex algorithmic logic.
Compute the Rank as a Percentage Python/Java solution
This problem is a SQL database question rather than a typical algorithm implemented in Python or Java. The expected solution uses MySQL window functions such as PERCENT_RANK(), or a combination of RANK() and COUNT() to compute the percentage rank for each row.
How to solve Compute the Rank as a Percentage in O(n)?
Achieving strict O(n) time is generally not possible because ranking requires ordering the dataset. SQL engines sort rows to determine their relative positions. As a result, most implementations using PERCENT_RANK or RANK have O(n log n) complexity due to sorting.
What is the best approach for Compute the Rank as a Percentage?
The best approach uses the SQL window function PERCENT_RANK() with an ORDER BY clause on the score column. It directly calculates the ranking percentage using the formula (rank − 1) / (total_rows − 1). This keeps the query short and readable while relying on the database engine to perform the ranking efficiently.
Is Compute the Rank as a Percentage asked at Google/Amazon/Meta?
Ranking and analytical SQL queries appear frequently in data engineering and analytics interviews at companies like Amazon, Meta, and Google. Problems involving window functions such as RANK, DENSE_RANK, and PERCENT_RANK test a candidate's ability to analyze datasets directly inside SQL.
What data structure is used in Compute the Rank as a Percentage?
The solution relies on relational database tables and SQL window functions rather than traditional data structures. Internally, the database engine sorts rows and processes them sequentially to compute ranking metrics.
What is the time complexity of Compute the Rank as a Percentage?
The time complexity is typically O(n log n). The database must sort rows by the score column before computing ranks. Window functions such as PERCENT_RANK() or RANK() operate on the sorted data, which makes sorting the dominant operation.

Ready to solve this problem?

Practice Compute the Rank as a Percentage with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary