Rank the students in each course
Hard · School database · Window functions, JOIN
Rank the students inside each course by marks, highest first. Students with equal marks share a rank and the next rank is skipped, so marks of 95, 95 and 88 rank 1, 1 and 3. Leave out enrollments with no marks.
Return the course name, the student's name, marks and the rank, in that order, sorted by course name, then rank, then student name.
Expected result
| course | student | marks | position |
|---|---|---|---|
| Biology | Fatima Sheikh | 93 | 1 |
| Biology | Diya Menon | 84 | 2 |
| Biology | Meera Patel | 75 | 3 |
| Biology | Ishaan Verma | 74 | 4 |
| Biology | Kavya Pillai | 62 | 5 |
| Chemistry | Priya Nair | 91 | 1 |
| Chemistry | Ananya Iyer | 85 | 2 |
| Chemistry | Meera Patel | 68 | 3 |
| Computer Science | Vikram Rao | 98 | 1 |
| Computer Science | Yuki Tanaka | 90 | 2 |
| Computer Science | Tanvi Bose | 87 | 3 |
| Computer Science | Lucas Martin | 66 | 4 |
| Economics | Tanvi Bose | 91 | 1 |
| Economics | Diya Menon | 80 | 2 |
| Economics | Sara Khan | 73 | 3 |
| English | Ananya Iyer | 92 | 1 |
| English | Priya Nair | 86 | 2 |
| English | Fatima Sheikh | 78 | 3 |
| English | Sara Khan | 78 | 3 |
| English | Kavya Pillai | 69 | 5 |
| Informatics Practices | Yuki Tanaka | 94 | 1 |
| Informatics Practices | Rahul Das | 83 | 2 |
| Informatics Practices | Aditya Joshi | 77 | 3 |
| Mathematics | Ishaan Verma | 95 | 1 |
| Mathematics | Vikram Rao | 95 | 1 |
| Mathematics | Aarav Sharma | 88 | 3 |
| Mathematics | Arjun Reddy | 82 | 4 |
| Mathematics | Aditya Joshi | 71 | 5 |
| Mathematics | Rohan Gupta | 67 | 6 |
| Mathematics | Kabir Singh | 58 | 7 |
First 30 of 37 rows.
The school tables
courses: 10 rows, first 5 shown
| id | name | teacher_id | credits |
|---|---|---|---|
| 1 | Physics | 1 | 4 |
| 2 | Chemistry | 2 | 4 |
| 3 | Mathematics | 3 | 5 |
| 4 | English | 4 | 3 |
| 5 | Biology | 5 | 4 |
enrollments: 40 rows, first 5 shown
| id | student_id | course_id | marks | enrolled_on |
|---|---|---|---|---|
| 1 | 1 | 1 | 88 | 2025-04-07 |
| 2 | 1 | 3 | 88 | 2025-04-07 |
| 3 | 2 | 2 | 91 | 2025-04-08 |
| 4 | 2 | 4 | 86 | 2025-04-08 |
| 5 | 3 | 1 | 72 | 2025-04-09 |
students: 24 rows, first 5 shown
| id | name | gender | class | section | city | dob | |
|---|---|---|---|---|---|---|---|
| 1 | Aarav Sharma | M | 12 | A | Delhi | 2008-05-14 | aarav.sharma@example.com |
| 2 | Priya Nair | F | 12 | A | Kochi | 2008-09-02 | priya.nair@example.com |
| 3 | Rohan Gupta | M | 12 | B | Delhi | 2008-01-23 | NULL |
| 4 | Ananya Iyer | F | 12 | B | Chennai | 2008-11-30 | ananya.iyer@example.com |
| 5 | Kabir Singh | M | 12 | C | Chandigarh | 2007-12-19 | kabir.singh@example.com |
teachers: 8 rows, first 5 shown
| id | name | subject | city | joined_on |
|---|---|---|---|---|
| 1 | Sunita Mehra | Physics | Delhi | 2012-06-01 |
| 2 | Rajesh Kumar | Chemistry | Delhi | 2015-07-15 |
| 3 | Lakshmi Krishnan | Mathematics | Chennai | 2010-04-01 |
| 4 | David Thomas | English | Kochi | 2018-06-20 |
| 5 | Farah Qureshi | Biology | Mumbai | 2016-08-01 |
-- Write your SQL here