Find Top Scoring Students - Practice Coding Problems

Find Top Scoring Students - Problem

Database Medium

You're working as a data analyst for a university's academic excellence program. The program wants to identify outstanding students who have not only completed all required courses in their major but have also achieved perfect grades (A) in every single one of them.

Given three database tables:

students - Contains student information including their major
courses - Contains course information organized by major
enrollments - Contains enrollment records with grades

Your task: Write a SQL query to find students who have taken all courses offered in their major and received an 'A' grade in every course.

This is a classic "division" problem in relational algebra - finding entities that are related to all entities in another set. Think of it as finding students who have a perfect academic record in their field of study.

Return the results ordered by student_id in ascending order.

Input & Output

example_1.sql — Basic Case

$ Input: students: [(1,'Alice','Computer Science'), (2,'Bob','Computer Science')] courses: [(101,'Algorithms',3,'Computer Science'), (102,'Data Structures',3,'Computer Science')] enrollments: [(1,101,'Fall 2023','A'), (1,102,'Fall 2023','A'), (2,101,'Fall 2023','B'), (2,102,'Fall 2023','A')]

› Output: [1]

💡 Note: Alice (ID 1) completed both CS courses with A grades. Bob (ID 2) got a B in Algorithms, so he doesn't qualify.

example_2.sql — Multiple Majors

$ Input: students: [(3,'Charlie','Mathematics'), (4,'David','Mathematics')] courses: [(103,'Calculus',4,'Mathematics'), (104,'Linear Algebra',4,'Mathematics')] enrollments: [(3,103,'Fall 2023','A'), (3,104,'Fall 2023','A'), (4,103,'Fall 2023','A'), (4,104,'Fall 2023','B')]

› Output: [3]

💡 Note: Charlie (ID 3) aced both Math courses. David (ID 4) got a B in Linear Algebra, disqualifying him.

example_3.sql — Incomplete Records

$ Input: students: [(5,'Eve','Physics')] courses: [(201,'Mechanics',3,'Physics'), (202,'Thermodynamics',3,'Physics')] enrollments: [(5,201,'Fall 2023','A')]

› Output: []

💡 Note: Eve only took one Physics course out of two required, so she doesn't qualify despite getting an A.

Constraints

1 ≤ student_id ≤ 1000
1 ≤ course_id ≤ 1000
Student names and course names are non-empty strings
Major names are consistent across tables
Grades are single uppercase letters (A, B, C, D, F)
Each student has exactly one major
Each course belongs to exactly one major

Visualization

Tap to expand

Understanding the Visualization

Identify Requirements

For each major, determine all required courses (like a graduation checklist)

Check Completion

Verify each student took all courses in their major

Verify Excellence

Ensure every grade is an 'A' - no exceptions allowed

Award Recognition

Students meeting both criteria earn top honors

Key Takeaway

🎯 Key Insight: This is a classic *relational division* problem - finding students whose set of A-grade courses exactly matches their major's complete course set. The optimal solution leverages SQL's GROUP BY and HAVING clauses to elegantly verify both completeness and excellence in a single query.

Asked in

G Google 45 f Meta 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses a strategic JOIN of all three tables, filtering for 'A' grades in courses that match the student's major, then applying GROUP BY with HAVING to ensure the count of A-grade courses equals the total courses in that major. This elegantly solves the relational division problem in a single, efficient query.

Common Approaches

Approach	Time	Space	Notes
✓ Count Comparison Approach	O(n log n)	O(n)	Count total courses per major, then count 'A' grades per student and compare
Nested Subqueries Approach	O(n³)	O(1)	Use multiple nested subqueries to check each condition separately
Window Function Approach (Optimal)	O(n)	O(1)	Use window functions to efficiently solve in a single optimized query

Count Comparison Approach — Algorithm Steps

Count total courses available per major
Count 'A' grade courses per student in their major
Join the counts and filter where they are equal
Order results by student_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Count Major Courses

Calculate total courses available in each major

Count Student A's

Count 'A' grades per student in their major

Compare Counts

Match students where A-grade count equals total course count

Code -

solution.c — C

// SQL solution using count comparison
char query[] = "\
WITH major_course_counts AS ( \
    SELECT major, COUNT(*) as total_courses \
    FROM courses \
    GROUP BY major \
), \
student_a_counts AS ( \
    SELECT s.student_id, s.major, COUNT(*) as a_courses \
    FROM students s \
    JOIN enrollments e ON s.student_id = e.student_id \
    JOIN courses c ON e.course_id = c.course_id AND c.major = s.major \
    WHERE e.grade = 'A' \
    GROUP BY s.student_id, s.major \
) \
SELECT sac.student_id \
FROM student_a_counts sac \
JOIN major_course_counts mcc ON sac.major = mcc.major \
WHERE sac.a_courses = mcc.total_courses \
ORDER BY sac.student_id;";

printf("%s\n", query);

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

One pass through each table plus sorting for final order

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for count aggregations

⚡ Linearithmic Space

38.2K Views

Medium Frequency

~25 min Avg. Time

1.6K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Count Comparison Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler