Average marks in each course
Medium · School database · JOIN, GROUP BY, COUNT, SUM, AVG
Find the average marks in each course. Enrollments with no marks yet (NULL) do not count, and courses that nobody has taken are left out.
Return the course name and its average marks rounded to 2 decimals with round, in that order, highest average first; equal averages by course name A to Z.
Expected result
| name | average_marks |
|---|---|
| Computer Science | 85.25 |
| Informatics Practices | 84.67 |
| Chemistry | 81.33 |
| Economics | 81.33 |
| English | 80.6 |
| Physics | 80.4 |
| Mathematics | 79.43 |
| Biology | 77.6 |
| Physical Education | 75 |
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