UptoSkills Logo
Login
Average Score per Student GroupMedium

Average Score per Student Group

MediumSQLPractice library · +20 XPcommon table expressionssqlaggregation

UptoSkills Arena needs to understand student performance. We have a record of student scores and their admission details. We want to identify groups of students who have achieved an average score above 75, considering groups based on the first letter of their name and their admission year.

Tables:

  • students: student_id (integer, PK), name (varchar(100)), admission_year (integer)
  • scores: score_id (integer, PK), student_id (integer, FK), score (integer)

Return the first letter of the student's name, the admission year, and the calculated average score for groups where the average score is strictly greater than 75. Order the results first by the first letter of the name (alphabetically), then by the admission year (ascending).

Handle NULLs: Assume name and admission_year in students are NOT NULL. score in scores is NOT NULL. Ties in average score within a group are not a concern as we only care about the average. Empty results are possible if no groups meet the criteria.

Tables

CREATE TABLE students (
  student_id integer PRIMARY KEY,
  name varchar(100) NOT NULL,
  admission_year integer NOT NULL
);
CREATE TABLE scores (
  score_id integer PRIMARY KEY,
  student_id integer NOT NULL REFERENCES students(student_id),
  score integer NOT NULL
);

Example data

INSERT INTO students (student_id, name, admission_year) VALUES
(1, 'Alice', 2023),
(2, 'Bob', 2023),
(3, 'Charlie', 2023),
(4, 'David', 2024),
(5, 'Eve', 2024),
(6, 'Frank', 2024),
(7, 'Grace', 2023),
(8, 'Heidi', 2024);

INSERT INTO scores (score_id, student_id, score) VALUES
(1, 1, 85),
(2, 1, 90),
(3, 2, 70),
(4, 2, 72),
(5, 3, 95),
(6, 3, 88),
(7, 4, 80),
(8, 4, 82),
(9, 5, 65),
(10, 5, 68),
(11, 6, 78),
(12, 6, 81),
(13, 7, 88),
(14, 7, 92),
(15, 8, 76),
(16, 8, 79);

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

Hints

Similar problems

More in: SQL and Databases problems