Which Course Fills Up the Fastest?
Timetabling at Fairhaven University has one large lecture theatre and has to decide which course gets it next term. The registrar's rule of thumb is simple: give it to whatever is drawing the most students.
The enrollment register is one row per student-course pairing, so a course's size is not written down at all — it is however many rows point at that course. And the register only carries a course_id; the human-readable title the timetable needs is kept in the course catalogue, on a separate table.
courses — one row per course on offer. credits is what the course is worth towards a degree.
| id | title | credits |
|---|---|---|
| 1 | Intro to Databases | 3 |
| 2 | Machine Learning | 4 |
| 3 | Linear Algebra | 3 |
| 4 | Data Visualization | 2 |
enrollments — one row per student-course pairing. course_id points at a row in courses.
| student_id | course_id | grade | semester |
|---|---|---|---|
| 1 | 1 | 3.7 | Fall2023 |
| 1 | 2 | 3.9 | Spring2024 |
| 2 | 1 | 3.2 | Fall2023 |
| 2 | 3 | 3.5 | Fall2023 |
| 3 | 2 | 4.0 | Spring2024 |
| 3 | 4 | 3.8 | Spring2024 |
| 4 | 3 | 2.9 | Fall2023 |
| 5 | 1 | 3.6 | Fall2023 |
| 5 | 2 | NULL | Spring2024 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single row with two columns, title and num_enrolled, for the course drawing the most students. Two Fairhaven courses are tied at the top of this register on three enrollments each, and the theatre cannot be given to both: settle it on the title, and hand the room to the one that comes first alphabetically. The same course must win every time the query is run.
Example output
| title | num_enrolled |
|---|---|
| Linear Algebra | 5 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in