Katkat
Back

SQL: Students Above Average and Passing Every Subject

ByteDanceBackend Engineer · Technical Screen
HardBackend EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests advanced SQL skills, including the use of window functions, conditional aggregation, and multi-level filtering to identify subsets of data that satisfy both global and per-category criteria.

Problem Statement

You are working with a database containing student performance records. Your goal is to identify students who meet two specific criteria: they must have passed every subject they are enrolled in, and their overall average grade across all subjects must be strictly higher than the global average grade of all students.

Database Schema

Table: grades

Column NameTypeDescription
student_idintUnique ID of the student
subjectvarcharName of the subject
scoreintNumeric grade (0-100)

Assume a passing grade is defined as score >= 60.

Task

Write a SQL query to return the student_id of students who:

  1. Scored at least 60 in every subject they have taken.
  2. Have an overall GPA (average of all their scores) strictly greater than the global average GPA of all students in the database.

Example

Input:

student_idsubjectscore
1Math80
1Science90
2Math50
2Science90
3Math70
3Science70

Output:

student_id
1

Explanation:

  • Student 1: Average is 85. Passed all. Global avg is 75. 85 > 75. Included.
  • Student 2: Failed Math. Excluded.
  • Student 3: Average is 70. Passed all, but 70 is not greater than the global average of 75. Excluded.

Constraints

  • student_id is the primary key for the table rows.
  • Return the result set ordered by student_id ascending.

Hints

Hint 1

Use a subquery or a Common Table Expression (CTE) to calculate the global average grade across all records first.

Hint 2

Use 'GROUP BY student_id' and 'HAVING' to filter out students who have any score less than 60.

Hint 3

Calculate the individual student's average within the same grouping and compare it against the global average calculated in the first hint.

Follow-up questions

  • How would you modify this query if the passing grade requirement varied by subject (e.g., Math requires 60, but Science requires 70)?
  • If the 'grades' table contains millions of rows, what indexes would you add to optimize the execution speed of this query?
  • How would you handle cases where a student has no grades recorded yet? Should they be included or excluded?