You are working with an online education platform that tracks student enrollments across various courses. The platform wants to identify students who are actively engaged in multiple courses at the same time, as these students are considered highly motivated learners and may qualify for special loyalty rewards or personalized learning paths. The database contains two tables: students — which holds basic student profile information including student_id , name , and email ; and enrollments — which records each enrollment event with columns enrollment_id , student_id , course_id , and enrolled_at . Each row in enrollments represents a unique student-course pairing. Your task is to write a PostgreSQL query that finds all students who are enrolled in more than one course . The result should include the student's student_id , their name , and the total number of courses they are enrolled in, aliased as course_count . The results should be ordered by course_count in descending order so the most active students appear first. Join the students and enrollments tables on student_id . Group the results by student_id and name . Use COUNT() to calculate how many courses each student is enrolled in. Use HAVING to filter only students with more than one enrollment. Return columns: student_id , name , course_count . Order by course_count DESC. Hint: Remember that filtering on aggregated values (like a count) must be done using HAVING , not WHERE . A common pitfall is trying to use WHERE course_count > 1 , which will cause an error because WHERE is evaluated before the aggregation step — always use HAVING for conditions on aggregate functions.

Table Setup (SQL Schema)

CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL
);

CREATE TABLE enrollments (
  enrollment_id SERIAL PRIMARY KEY,
  student_id INT NOT NULL REFERENCES students(student_id),
  course_id INT NOT NULL,
  enrolled_at DATE NOT NULL
);

INSERT INTO students (name, email) VALUES
  ('Alice Johnson', '[email protected]'),
  ('Bob Smith', '[email protected]'),
  ('Carol White', '[email protected]'),
  ('David Brown', '[email protected]'),
  ('Eva Martinez', '[email protected]'),
  ('Frank Lee', '[email protected]');

INSERT INTO enrollments (student_id, course_id, enrolled_at) VALUES
  (1, 101, '2024-01-10'),
  (1, 102, '2024-01-15'),
  (1, 103, '2024-02-01'),
  (2, 101, '2024-01-12'),
  (2, 104, '2024-01-20'),
  (3, 102, '2024-02-05'),
  (4, 103, '2024-01-08'),
  (4, 105, '2024-01-09'),
  (4, 106, '2024-02-10'),
  (4, 107, '2024-03-01'),
  (5, 101, '2024-03-03'),
  (6, 104, '2024-03-05');