Implement Spring MVC to find top-enrolled course

Quick Overview

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.

Company: Disney

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: 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.

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).

Tables

courses(course_id INT, course_name VARCHAR(100))

students(student_id INT, student_name VARCHAR(100))

enrollments(course_id INT, student_id INT)

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.