Find the top 100 students

Warm-up · aggregation, joins, sorting

Given 3 tables, students(id, name), courses(id, name), grades(id, course_id, student_id, grade),

find the top 100 students based on their average grade, sorted descending by the average grade.

If multiple students have the same average grade, sort them by name in ascending order.

Output the columns (name, average_grade).

Expected columns

name, average_grade

Hints

Hint 1 — one row per student

Join students to grades on students.id = grades.student_id and GROUP BY students.id. AVG(grades.grade) then gives one average per student. SHOW INDEX FROM grades shows which of those join columns are indexed.

Hint 2 — the tie-break

ORDER BY average_grade DESC, name ASC, then LIMIT 100. Without the second sort key, students with equal averages come back in an arbitrary order.