Watch the video solution for Compute the Rank as a Percentage, a medium level problem involving Database. This walkthrough by Everyday Data Science has 471 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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
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.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| PERCENT_RANK Window Function | O(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. |
