SQL interview question: Count Rows per Group
Course Enrollment Numbers
The education team wants to see which courses are most popular.
Count how many students are enrolled in each course that has at least one enrollment. Join on course_id.
Return course_name and student_count. Order by student count descending.
Skills: GROUP BY and COUNT with ORDER BY
Tables
enrollments 47 rows
- enrollment_id integer PK
- student_id integer → students
- course_id integer → courses
- semester text
- grade text
| enrollment_id | student_id | course_id | semester | grade |
|---|---|---|---|---|
| 1 | 1 | 1 | Fall 2024 | A |
| 2 | 1 | 3 | Fall 2024 | A |
| 3 | 1 | 5 | Fall 2024 | A- |
courses 15 rows
- course_id integer PK
- course_name text
- credits integer
- department text
| course_id | course_name | credits | department |
|---|---|---|---|
| 1 | SQL Basics | 3 | Computer Science |
| 2 | Python 101 | 3 | Computer Science |
| 3 | Data Structures | 4 | Computer Science |