LeetCode 3188 - Find Top Scoring Students II

This problem asks us to identify students who satisfy a combination of academic requirements based on their major, completed courses, grades, and overall GPA. We are given three tables: - The students table stores each student's identity and declared major.

leetcodeharddatabase

LeetCode Problem 3188

Difficulty: 🔴 Hard
Topics: Database

Solution

Problem Understanding

This problem asks us to identify students who satisfy a combination of academic requirements based on their major, completed courses, grades, and overall GPA.

We are given three tables:

  • The students table stores each student's identity and declared major.
  • The courses table stores information about courses, including which major they belong to and whether they are mandatory or elective.
  • The enrollments table stores every course enrollment for every student, including the grade received and GPA earned for that enrollment.

The goal is to return the student_id values of students who satisfy all of the following conditions:

  1. The student has completed every mandatory course in their own major.
  2. The student earned grade A in every mandatory course for their major.
  3. The student has taken at least two elective courses in their major.
  4. The student earned at least grade B in those elective courses.
  5. The student's average GPA across all enrolled courses, including courses outside their major, is at least 2.5.

The result must be sorted by student_id in ascending order.

A very important detail is that the validation only applies to courses belonging to the student's own major. For example, a Computer Science student only needs to satisfy Computer Science mandatory and elective requirements. Courses from other majors are irrelevant for the mandatory and elective checks, but they still count toward GPA averaging.

Another subtle detail is that the problem says students must have "taken all mandatory courses". This means we must compare the number of mandatory courses completed by the student against the total number of mandatory courses defined for that major in the courses table. A student cannot qualify by taking only some mandatory courses, even if all taken courses received grade A.

Similarly, for electives, we only count elective courses from the student's own major. The student must complete at least two such electives with grade at least B.

The input size is not explicitly specified, but this is a database problem intended for SQL optimization. That means we should avoid repeated scans or nested subqueries that explode combinatorially. Aggregation with grouping and conditional counting is the intended direction.

Important edge cases include:

  • Majors with many mandatory courses where a student misses only one.
  • Students who took all mandatory courses but received a non-A grade in one of them.
  • Students who took electives but fewer than two.
  • Students with enough courses but insufficient average GPA.
  • Students taking courses outside their major, which should count toward GPA but not toward major requirements.
  • Duplicate enrollments across semesters. Since the primary key includes semester, the same course can theoretically appear multiple times for the same student in different semesters. We must ensure counting logic behaves correctly. This problem asks us to identify students who satisfy a fairly strict combination of academic requirements across multiple database tables. The challenge is not simply checking grades or counting courses independently, because all conditions are interconnected through the student's major and enrollment history.

The students table stores each student's identity and declared major. The courses table describes every course, including which major it belongs to and whether it is mandatory or elective. The enrollments table records which student took which course, the semester, the grade earned, and the GPA contribution for that enrollment.

A student qualifies for the final result only if all of the following are true:

  1. The student has completed every mandatory course for their major.
  2. The student earned grade A in every mandatory major course.
  3. The student completed at least two elective courses for their major.
  4. The student earned at least grade B in those elective courses.
  5. The student's average GPA across all enrollments, including courses outside their major, is at least 2.5.

The result should contain only the student_id values of qualifying students, sorted in ascending order.

The key detail is that the mandatory and elective checks apply only to courses belonging to the student's own major. However, the GPA condition applies across all enrollments regardless of major.

The constraints implied by the schema suggest that a student may have multiple enrollments, multiple semesters, and potentially courses outside their department. Because this is a SQL problem categorized as Hard, the intended solution is likely based on aggregation, conditional counting, and careful joins rather than procedural iteration.

Several edge cases are important:

  • A student may miss even one mandatory course. That student must be excluded.
  • A student may take all mandatory courses but receive a B in one mandatory course. That student must also be excluded.
  • A student may have only one qualifying elective course. The requirement explicitly demands at least two.
  • A student may take electives but receive grades below B.
  • GPA must be averaged across every enrollment row, not only major-related courses.
  • Students can take courses outside their major, which should not affect mandatory or elective validation but should affect GPA.

Approaches

Brute Force Approach

A brute force solution would process each student independently and repeatedly scan the tables to validate every requirement.

For each student, we could:

  1. Retrieve all mandatory courses for the student's major.
  2. Check whether the student enrolled in every mandatory course.
  3. Verify every mandatory course grade is A.
  4. Retrieve all elective courses for the student's major.
  5. Count how many electives were completed with grade at least B.
  6. Compute the student's average GPA across all enrollments.
  7. Add the student to the answer if all conditions pass.

This approach is logically correct because it explicitly checks every requirement directly against the underlying data. However, it is inefficient because each student triggers multiple scans and joins across the same tables.

If there are S students, C courses, and E enrollments, repeatedly filtering and joining for every student becomes expensive. The same course information is recomputed many times.

Key Insight for the Optimal Approach

The important observation is that this problem is fundamentally an aggregation problem.

Instead of validating students one by one, we can:

  • Join the tables once,
  • Group by student,
  • Compute all required statistics using conditional aggregation.

SQL aggregation functions such as:

  • COUNT(DISTINCT ...)
  • AVG(...)
  • CASE WHEN ...

allow us to compute all conditions simultaneously in a single grouped query.

The core insight is that every requirement can be expressed as a count comparison:

  • Number of mandatory courses completed with A
  • Total number of mandatory courses for the major
  • Number of elective courses completed with grade at least B
  • Average GPA

Once all these aggregates are computed, we simply filter groups using a HAVING clause.

Approach Comparison

A brute-force solution would iterate through every student individually and perform multiple independent checks.

For each student, we could first retrieve all mandatory courses for the student's major. Then we would scan the student's enrollments to verify that every required course was taken and that every corresponding grade was A.

Next, we would retrieve all elective courses for the student's major and count how many of those the student completed with grades of at least B.

Finally, we would compute the student's average GPA across all enrollments.

This approach is logically straightforward because it directly mirrors the problem statement. However, it becomes inefficient because the same tables are repeatedly scanned for every student. If there are S students, C courses, and E enrollments, repeatedly filtering and searching through enrollments creates substantial redundant work.

Optimal Approach

The key observation is that this problem is fundamentally an aggregation problem. Instead of validating students one by one procedurally, we can compute all required metrics in grouped SQL queries.

We can:

  • Count how many mandatory courses exist for each major.
  • Count how many mandatory courses each student completed with grade A.
  • Count how many elective courses each student completed with grade at least B.
  • Compute each student's average GPA.

Once those aggregates are available, we simply compare the computed counts against the required counts.

The main insight is that SQL excels at grouped conditional aggregation. Using COUNT, AVG, and CASE WHEN, we can evaluate all conditions simultaneously in a single grouped query.

Approach Time Complexity Space Complexity Notes
Brute Force O(S × (C + E)) O(C + E) Repeated scans for every student
Optimal O(C + E) O(C + E) Single grouped aggregation query

Algorithm Walkthrough

  1. First, determine how many mandatory courses exist for each major.

We group the courses table by major and count courses where mandatory = 'Yes'. This gives the required mandatory course count for every major. 2. Join students with enrollments and courses.

We connect:

  • students.student_id = enrollments.student_id
  • enrollments.course_id = courses.course_id

This gives us access to:

  • the student's major,
  • the course's major,
  • whether the course is mandatory,
  • the student's grade,
  • the GPA value.
  1. Restrict major requirement checks to the student's own major.

We only consider courses where:

students.major = courses.major

because only courses from the student's declared major count toward mandatory and elective requirements. 4. Count completed mandatory courses with grade A.

Using conditional aggregation:

COUNT(DISTINCT CASE
    WHEN courses.mandatory = 'Yes'
         AND enrollments.grade = 'A'
    THEN courses.course_id
END)

This counts mandatory major courses passed with grade A. 5. Compare against the total mandatory course count.

We compare the previous count against the precomputed total mandatory course count for that major.

If the counts match, the student has completed every mandatory course with grade A. 6. Count qualifying elective courses.

Using another conditional aggregation:

COUNT(DISTINCT CASE
    WHEN courses.mandatory = 'No'
         AND enrollments.grade IN ('A', 'B')
    THEN courses.course_id
END)

This counts elective major courses completed with acceptable grades. 7. Compute average GPA.

We compute:

AVG(enrollments.GPA)

across all enrollments, including courses outside the student's major. 8. Apply filtering conditions in the HAVING clause.

We keep only students where:

  • mandatory course count matches required count,
  • elective count is at least 2,
  • average GPA is at least 2.5.
  1. Return results ordered by student_id.

Why it works

The algorithm works because every requirement is translated into an aggregate property over a student's enrollments.

The grouped query guarantees that all enrollments belonging to the same student are processed together. Conditional aggregation isolates mandatory and elective courses independently, while count comparisons ensure completeness requirements are satisfied exactly.

Because the mandatory course count is compared against the authoritative total from the courses table, a student cannot qualify while missing required courses. Similarly, elective and GPA conditions are enforced directly through aggregate filters. | Optimal | O(C + E + S) | O(S) | Uses grouped aggregation and joins efficiently |

Algorithm Walkthrough

  1. First, compute the number of mandatory courses for each major.

We need a baseline requirement for every major. For example, Computer Science may require 2 mandatory courses while Mathematics may also require 2. This allows us to later verify whether a student completed all required courses. 2. Join students with enrollments and courses.

This combined dataset gives us complete visibility into:

  • Which student took which course
  • Whether the course belongs to the student's major
  • Whether the course is mandatory or elective
  • The student's grade in that course

This join is the foundation for all subsequent aggregation logic. 3. Compute mandatory-course completion counts.

For each student, count how many mandatory courses in their own major were completed with grade A.

We use conditional aggregation:

COUNT(
    DISTINCT CASE
        WHEN c.mandatory = 'Yes' AND e.grade = 'A'
        THEN c.course_id
    END
)

Using DISTINCT prevents duplicate counting if unusual duplicate enrollment patterns exist. 4. Compute elective-course completion counts.

For each student, count how many elective major courses were completed with grades at least B.

The grade requirement means both A and B are acceptable. 5. Compute average GPA.

We calculate:

AVG(e.GPA)

across all enrollments, including courses outside the student's major. 6. Compare mandatory completion counts against required counts.

A student qualifies only if:

  • mandatory_A_count = total_mandatory_courses
  • elective_good_count >= 2
  • average_gpa >= 2.5
  1. Return student IDs in ascending order.

Why it works

The correctness comes from the fact that every required condition is converted into a measurable aggregate invariant.

The mandatory-course requirement is satisfied exactly when the number of mandatory courses completed with grade A equals the total number of mandatory courses defined for the student's major.

The elective requirement is satisfied exactly when the student has at least two qualifying elective enrollments.

The GPA requirement is satisfied exactly when the computed average across all enrollment rows is at least 2.5.

Since all conditions are evaluated independently and combined with logical conjunction, every returned student satisfies all requirements, and every qualifying student is included.

Python Solution

class Solution:
    def findTopScoringStudents(self):
        return """
        WITH mandatory_counts AS (
            SELECT
                major,
                COUNT(*) AS total_mandatory
            FROM courses
            WHERE mandatory = 'Yes'
            GROUP BY major
        )

        SELECT
            s.student_id
        FROM students s
        JOIN mandatory_counts mc
            ON s.major = mc.major
        JOIN enrollments e
            ON s.student_id = e.student_id
        JOIN courses c
            ON e.course_id = c.course_id
        GROUP BY
            s.student_id,
            s.major,
            mc.total_mandatory
        HAVING
            COUNT(DISTINCT CASE
                WHEN c.major = s.major
                     AND c.mandatory = 'Yes'
                     AND e.grade = 'A'
                THEN c.course_id
            END) = mc.total_mandatory
            AND
            COUNT(DISTINCT CASE
                WHEN c.major = s.major
                     AND c.mandatory = 'No'
                     AND e.grade IN ('A', 'B')
                THEN c.course_id
            END) >= 2
            AND
            AVG(e.GPA) >= 2.5
        ORDER BY s.student_id
        """

The solution begins with a Common Table Expression named mandatory_counts. This precomputes how many mandatory courses exist for every major. Without this step, we would repeatedly recompute mandatory totals during aggregation.

Next, we join all three tables together so each enrollment row contains both student information and course metadata.

The query groups rows by student. Once grouped, conditional aggregation is used to measure whether the student satisfies each rule.

The first conditional count checks mandatory courses with grade A. Using DISTINCT prevents duplicate counting if the same course appears across multiple semesters.

The second conditional count checks elective courses completed with acceptable grades.

Finally, the HAVING clause filters only students satisfying all constraints, and the results are ordered by student_id.

Go Solution

package main

type Solution struct{}

func (s Solution) FindTopScoringStudents() string {
	return `
# Write your MySQL query statement below

WITH mandatory_counts AS (
    SELECT
        major,
        COUNT(*) AS total_mandatory
    FROM courses
    WHERE mandatory = 'Yes'
    GROUP BY major
)

SELECT
    s.student_id
FROM students s
JOIN mandatory_counts mc
    ON s.major = mc.major
JOIN enrollments e
    ON s.student_id = e.student_id
JOIN courses c
    ON e.course_id = c.course_id
GROUP BY
    s.student_id,
    s.major,
    mc.total_mandatory
HAVING
    COUNT(DISTINCT CASE
        WHEN c.major = s.major
             AND c.mandatory = 'Yes'
             AND e.grade = 'A'
        THEN c.course_id
    END) = mc.total_mandatory
    AND
    COUNT(DISTINCT CASE
        WHEN c.major = s.major
             AND c.mandatory = 'No'
             AND e.grade IN ('A', 'B')
        THEN c.course_id
    END) >= 2
    AND
    AVG(e.GPA) >= 2.5
ORDER BY s.student_id
`
}

The Go version is structurally identical to the Python version because LeetCode database problems typically return raw SQL strings.

One small difference is that Go uses raw string literals enclosed in backticks, which makes multiline SQL much cleaner and avoids escaping quotes or line breaks. JOIN mandatory_counts mc ON s.major = mc.major GROUP BY s.student_id, s.major, mc.total_mandatory HAVING COUNT( DISTINCT CASE WHEN c.major = s.major AND c.mandatory = 'Yes' AND e.grade = 'A' THEN c.course_id END ) = mc.total_mandatory AND COUNT( DISTINCT CASE WHEN c.major = s.major AND c.mandatory = 'No' AND e.grade IN ('A', 'B') THEN c.course_id END ) >= 2 AND AVG(e.GPA) >= 2.5 ORDER BY s.student_id;


The solution begins by constructing a Common Table Expression named `mandatory_counts`. This computes how many mandatory courses exist for each major.

The main query joins all three tables so that each enrollment row contains both student and course context.

The `GROUP BY` clause groups all rows belonging to the same student. Once grouped, conditional aggregation evaluates the required conditions.

The first conditional count validates mandatory courses. It only counts courses that:

- belong to the student's major,
- are mandatory,
- and were completed with grade `A`.

That count must exactly equal the total number of required mandatory courses for the major.

The second conditional count validates electives. It counts elective courses in the student's major where the grade is either `A` or `B`. The result must be at least `2`.

Finally, `AVG(e.GPA)` computes the GPA requirement across all enrollments.

The query then returns qualifying students ordered by `student_id`.

## Go Solution

```go
// Write your MySQL query statement below

WITH mandatory_counts AS (
    SELECT
        major,
        COUNT(*) AS total_mandatory
    FROM courses
    WHERE mandatory = 'Yes'
    GROUP BY major
)

SELECT
    s.student_id
FROM students s
JOIN enrollments e
    ON s.student_id = e.student_id
JOIN courses c
    ON e.course_id = c.course_id
JOIN mandatory_counts mc
    ON s.major = mc.major
GROUP BY s.student_id, s.major, mc.total_mandatory
HAVING
    COUNT(
        DISTINCT CASE
            WHEN c.major = s.major
                 AND c.mandatory = 'Yes'
                 AND e.grade = 'A'
            THEN c.course_id
        END
    ) = mc.total_mandatory
    AND
    COUNT(
        DISTINCT CASE
            WHEN c.major = s.major
                 AND c.mandatory = 'No'
                 AND e.grade IN ('A', 'B')
            THEN c.course_id
        END
    ) >= 2
    AND
    AVG(e.GPA) >= 2.5
ORDER BY s.student_id;

Because this is a Database problem on LeetCode, both the Python and Go submissions are identical SQL queries. The language selection only changes the execution environment, not the actual solution.

No Go-specific memory handling or integer-overflow concerns apply here because the logic is executed entirely by the SQL engine.

Worked Examples

Example 1

Consider Alice, student 1.

Her major is Computer Science.

Mandatory Computer Science courses are:

Course ID Mandatory
101 Yes
102 Yes

Elective Computer Science courses are:

Course ID Mandatory
105 No
107 No

Alice enrollments:

Course Grade GPA
101 A 4.0
102 A 4.0
105 A 4.0
107 B 3.5

Mandatory aggregation:

Condition Count
Mandatory courses with A 2
Total mandatory courses 2

The counts match, so mandatory requirements pass.

Elective aggregation:

Condition Count
Electives with A or B 2

Elective requirement passes.

Average GPA:

(4.0 + 4.0 + 4.0 + 3.5) / 4 = 3.875

Since 3.875 >= 2.5, Alice qualifies. The mandatory Computer Science courses are:

Course ID Course Mandatory
101 Algorithms Yes
102 Data Structures Yes

Alice's enrollments are:

Course ID Grade Counts Toward Requirement?
101 A Mandatory satisfied
102 A Mandatory satisfied
105 A Elective satisfied
107 B Elective satisfied

Now evaluate conditions step by step.

Step 1: Mandatory Count

Metric Value
Required mandatory courses 2
Mandatory courses completed with A 2

Condition satisfied.

Step 2: Elective Count

Metric Value
Elective courses with A/B 2

Condition satisfied.

Step 3: GPA

GPA Values
4.0
4.0
4.0
3.5

Average GPA:

$\frac{4.0+4.0+4.0+3.5}{4}=3.875$

Since 3.875 >= 2.5, the GPA condition is satisfied.

Alice is included in the result.

Example 2

Consider Bob, student 2.

Enrollments:

| Course | Grade | His enrollments:

Course ID Grade
101 A
102 B

Mandatory validation:

Course Required Grade Actual Grade
101 A A
102 A B

Only one mandatory course satisfies the A condition.

Metric Value
Mandatory A count 1
Required mandatory count 2

Since the counts do not match, Bob is rejected.

Metric Value
Required mandatory courses 2
Mandatory courses completed with A 1

Bob received a B in course 102, so he does not satisfy the mandatory-course requirement.

He is excluded immediately.

Example 3

Consider Charlie, student 3.

Mathematics mandatory courses: Mandatory Mathematics courses:

Course ID Mandatory
103 Yes
104 Yes

Charlie enrollments:

Course Grade
103 A
104 A
106 A
108 B

Mandatory validation succeeds because both required courses received A.

Elective validation succeeds because two electives received grades at least B.

Average GPA:

(4.0 + 4.0 + 4.0 + 3.5) / 4 = 3.875

Charlie qualifies. Charlie's enrollments:

Course ID Grade Category
103 A Mandatory
104 A Mandatory
106 A Elective
108 B Elective

Mandatory requirement:

Metric Value
Required mandatory courses 2
Mandatory A count 2

Elective requirement:

Metric Value
Elective A/B count 2

GPA calculation:

$\frac{4.0+4.0+4.0+3.5}{4}=3.875$

Charlie satisfies all conditions and is included.

Example 4

Consider David, student 4.

Enrollments:

| Course | Grade | His enrollments:

Course ID Grade
103 B
104 B

Mandatory requirement fails immediately because mandatory courses require grade A.

David is rejected. Mandatory validation:

Metric Value
Required mandatory courses 2
Mandatory courses completed with A 0

David fails the mandatory-grade requirement and is excluded.

Complexity Analysis

Measure Complexity Explanation
Time O(C + E) Single pass aggregation over courses and enrollments
Space O(C) Stores grouped mandatory course counts

The dominant cost comes from scanning and joining the enrollments and courses tables. Aggregation is performed once per student group, making the solution highly efficient for relational database execution engines.

The auxiliary space is primarily from the grouped mandatory_counts Common Table Expression and database aggregation structures. | Time | O(C + E + S) | Each table is scanned and aggregated efficiently | | Space | O(S) | Grouping structures are maintained per student |

The dominant work comes from joining and aggregating the tables. Modern SQL engines optimize grouped aggregation very efficiently using hash aggregation or sorting strategies.

The courses table is scanned once to compute mandatory counts. The joined enrollment dataset is then grouped once by student. No nested repeated scans are required, which keeps the solution scalable.

Test Cases

# Example case from the prompt
assert run_query(...) == [1, 3]  # standard validation case

# Student missing one mandatory course
assert run_query(...) == []  # incomplete mandatory requirements

# Student has all mandatory courses but wrong grade
assert run_query(...) == []  # mandatory course requires grade A

# Student has only one elective
assert run_query(...) == []  # requires at least two electives

# Student GPA below threshold
assert run_query(...) == []  # average GPA must be at least 2.5

# Student takes outside-major courses
assert run_query(...) == [1]  # outside-major courses count only toward GPA

# Duplicate course across semesters
assert run_query(...) == [1]  # DISTINCT prevents double counting

# Multiple students qualify
assert run_query(...) == [1, 2, 5]  # ordering must be ascending

# No students qualify
assert run_query(...) == []  # empty result case

# Elective grade lower than B
assert run_query(...) == []  # elective must be A or B

Test Summary

Test Why
Standard example Validates normal successful behavior
Missing mandatory course Ensures completeness checking works
Wrong mandatory grade Ensures mandatory courses require A
Only one elective Validates elective count constraint
Low GPA Verifies GPA filtering
Outside-major courses Confirms GPA includes all courses
Duplicate semesters Ensures DISTINCT logic is correct
Multiple qualifying students Validates sorting and grouping
Empty result Ensures no false positives
Elective grade below B Validates elective grade threshold

Edge Cases

Student Retakes the Same Course Across Semesters

Because the primary key includes semester, a student may appear multiple times for the same course. A naive COUNT(*) could incorrectly inflate mandatory or elective counts.

The implementation avoids this by using:

COUNT(DISTINCT c.course_id)

This guarantees each course contributes only once toward qualification requirements.

Students Taking Courses Outside Their Major

A student may enroll in courses belonging to another major. These courses should not count toward mandatory or elective requirements, but they should still affect GPA.

The implementation handles this carefully by:

  • filtering major requirement checks with:
c.major = s.major
  • computing GPA using all enrollment rows without filtering.

This separation prevents accidental qualification from unrelated courses.

Majors With Many Mandatory Courses

A student must complete every mandatory course, not just some of them.

A naive solution might only check whether all completed mandatory courses have grade A, which would incorrectly allow students missing required courses.

The implementation solves this by comparing:

mandatory_courses_completed_with_A
=
total_mandatory_courses

This guarantees both completeness and grade correctness simultaneously.

Example case from prompt

assert run_solution(...) == [1, 3] # standard validation case

Student missing one mandatory course

assert run_solution(...) == [] # incomplete mandatory requirements

Student has all mandatory courses but wrong grade

assert run_solution(...) == [] # mandatory course grade below A

Student has only one elective

assert run_solution(...) == [] # needs at least two electives

Student has two electives but one grade below B

assert run_solution(...) == [] # elective grade requirement fails

GPA below threshold

assert run_solution(...) == [] # average GPA below 2.5

Student takes outside-major courses

assert run_solution(...) == [1] # outside-major courses still count toward GPA

Student has extra mandatory retake attempts

assert run_solution(...) == [1] # DISTINCT prevents duplicate counting

Multiple qualifying students

assert run_solution(...) == [1, 2, 5] # verifies ordering and multiple outputs

No students qualify

assert run_solution(...) == [] # empty result handling


| Test | Why |
| --- | --- |
| Prompt example | Verifies baseline correctness |
| Missing mandatory course | Ensures all mandatory courses are required |
| Wrong mandatory grade | Verifies strict `A` requirement |
| One elective only | Validates minimum elective count |
| Elective below B | Ensures elective grade filtering works |
| Low GPA | Verifies GPA threshold |
| Outside-major courses | Confirms GPA includes all courses |
| Retaken courses | Prevents duplicate counting bugs |
| Multiple valid students | Verifies sorting and aggregation |
| Empty result | Ensures graceful handling when nobody qualifies |

## Edge Cases

One important edge case occurs when a student completes every mandatory course but receives a non-`A` grade in even one of them. A naive implementation might simply verify course completion without validating grades carefully. This solution handles the issue by counting only mandatory courses where the grade is exactly `A`. Therefore, the mandatory count will not match the required total unless every required course satisfies the grade condition.

Another subtle edge case involves duplicate enrollments or retaken courses. Without `DISTINCT`, a student who retook a course multiple times could accidentally inflate their counts. The query avoids this problem by counting distinct `course_id` values inside the conditional aggregation expressions.

A third important edge case occurs when students take many courses outside their major. Those courses should not affect mandatory or elective validation, but they must still influence the GPA calculation. The query correctly separates these concerns by applying major-based filters only inside the conditional counts while computing `AVG(e.GPA)` across all enrollment rows.

A final edge case occurs when no students satisfy the criteria. Some implementations accidentally return incorrect rows because of join behavior or improper aggregation logic. This solution naturally returns an empty result set when no grouped student satisfies all `HAVING` conditions.