Coding questions · Disney
Implement Spring MVC to find top-enrolled course
This question evaluates practical skills in building a Spring MVC REST service with JPA and SQL aggregation to identify the most-enrolled course, along with competencies in data modeling, repository/DAO design, efficient aggregation queries, deterministic tie-breaking, error handling, indexing strategy, and endpoint testing, and it belongs to the Data Manipulation (SQL/Python) domain. It is commonly asked to assess an applicant's ability to integrate persistence with a web layer, reason about database performance and time/space complexity, and demonstrate both practical implementation ability and conceptual understanding of schema/indexing trade-offs and failure scenarios.
Implement a Spring MVC service that returns the course with the highest number of enrolled students from a relational database pre-populated by provided INSERT scripts. Define JPA entities for Course, Student, and (if many-to-many) Enrollment; create repositories/DAOs, a service layer, and a REST controller endpoint GET /courses/most-popular that returns the winning course and its enrollment count. Write the aggregation query efficiently (e.g., COUNT(*) GROUP BY course_id ORDER BY count DESC LIMIT 1), handle ties deterministically (e.g., break by course_id or name), and include error handling for empty datasets and database failures. Describe your schema assumptions, indexing strategy, and time/space complexity of the query; discuss how you would test the endpoint.
Find the Course with the Highest Number of Enrolled Students
You are given three tables modeling a course enrollment system: COURSES, STUDENTS, and ENROLLMENTS (a many-to-many relationship between students and courses). Write a SQL query to return the single course with the highest number of enrolled students. The result should include the course_id, course_name, and the enrollment_count (number of students enrolled in that course).
If multiple courses are tied for the highest enrollment count, break ties deterministically by choosing the course with the smallest course_id. Courses with zero enrollments should not win unless all courses have zero enrollments (in which case any one with zero would be acceptable, but your query does not need special handling beyond the normal aggregation logic).
Use an efficient aggregation approach (e.g., COUNT(*) with GROUP BY and appropriate ORDER BY and LIMIT).
Table courses
| column | type | constraints |
|---|---|---|
| course_id | INT | PRIMARY KEY, NOT NULL |
| course_name | VARCHAR(100) | NOT NULL |
Sample data:
| course_id | course_name |
|---|---|
| 1 | Math 101 |
| 2 | History 201 |
| 3 | Biology 301 |
| 4 | Philosophy 101 |
Table students
| column | type | constraints |
|---|---|---|
| student_id | INT | PRIMARY KEY, NOT NULL |
| student_name | VARCHAR(100) | NOT NULL |
Sample data:
| student_id | student_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | Diana |
| 5 | Evan |
| 6 | Fiona |
Table enrollments
| column | type | constraints |
|---|---|---|
| course_id | INT | NOT NULL |
| student_id | INT | NOT NULL |
Sample data:
| course_id | student_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 5 |
| 2 | 6 |
| 3 | 1 |
| 3 | 4 |
Expected output
Return a single row with the course that has the highest number of enrolled students. Include the course_id, course_name, and enrollment_count. If there is a tie on enrollment_count, return the course with the smallest course_id.
| course_id | course_name | enrollment_count |
|---|---|---|
| 1 | Math 101 | 3 |
Hints
- Join COURSES to ENROLLMENTS and aggregate by course_id to count students.
- Use ORDER BY COUNT(e.student_id) DESC, course_id ASC with LIMIT 1 to break ties deterministically.