SQL interview question: Ranking With Ties
Student GPA Ranking with Ties
Academic honors requires ranking students by GPA, handling ties properly so no ranks are skipped.
Rank students by GPA using DENSE_RANK. Also show what percentile they fall in using PERCENT_RANK.
Return name, gpa, gpa_rank (DENSE_RANK), and percentile (ROUND PERCENT_RANK * 100 to 1 decimal). Limit to 15.
Skills: DENSE_RANK and PERCENT_RANK together
Tables
students 25 rows
- student_id integer PK
- name text
- email text
- grade_level integer
- gpa real
- enrollment_date text
| student_id | name | grade_level | gpa | enrollment_date | |
|---|---|---|---|---|---|
| 1 | Alice Johnson | [email protected] | 12 | 3.9 | 2024-09-01 |
| 2 | Bob Smith | [email protected] | 12 | 3.7 | 2024-09-01 |
| 3 | Carol Williams | [email protected] | 12 | 3.8 | 2024-09-01 |