UptoSkills Logo
Login
Find Students with Above-Average GPA in Their DepartmentMedium

Find Students with Above-Average GPA in Their Department

MediumSQLPractice library · +20 XPsubqueriessqlaggregation

UptoSkills Arena wants to identify high-performing students. You are given a list of students, their departments, and their GPAs. Write a query to find all students whose GPA is strictly greater than the average GPA of all students in their department.

Tables:

  • students: student_id (integer, PK), name (varchar(100)), department (varchar(50)), gpa (numeric(3,2))

Return the student_id, name, and gpa of these students. The order does not matter. Handle ties by including all students with the same GPA if they meet the condition. NULL GPAs should be ignored in average calculations and in the final result.

Tables

CREATE TABLE students (
  student_id integer PRIMARY KEY,
  name varchar(100) NOT NULL,
  department varchar(50) NOT NULL,
  gpa numeric(3,2)
);

Example data

INSERT INTO students (student_id, name, department, gpa)
VALUES
(1, 'Alice', 'Computer Science', 3.8),
(2, 'Bob', 'Computer Science', 3.5),
(3, 'Charlie', 'Computer Science', 3.9),
(4, 'David', 'Electronics', 3.7),
(5, 'Eve', 'Electronics', 3.6),
(6, 'Frank', 'Mechanical', 3.4),
(7, 'Grace', 'Mechanical', 3.4),
(8, 'Heidi', 'Computer Science', NULL),
(9, 'Ivy', 'Electronics', 3.8),
(10, 'Jack', 'Mechanical', NULL);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems