UptoSkills Logo
Login
Rank Students by Cumulative Score in Each CollegeMedium

Rank Students by Cumulative Score in Each College

MediumSQLPractice library · +20 XPwindow functionssqlranking

Acme College wants to identify top-performing students based on their cumulative scores across all their subjects. You need to rank students within each college based on their total accumulated scores.

Tables:

  • students: student_id (INT), name (VARCHAR(100)), college_id (INT)
  • scores: score_id (INT), student_id (INT), subject (VARCHAR(50)), score (INTEGER)

Return the student_id, college_id, and their rank within the college, ordered by college_id and then by rank.

Handle ties by assigning the same rank to students with the same cumulative score, and the next rank should be skipped (e.g., 1, 2, 2, 4). NULL scores are not possible as the score column is NOT NULL.

Tables

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  college_id INTEGER NOT NULL
);
CREATE TABLE scores (
  score_id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  subject VARCHAR(50) NOT NULL,
  score INTEGER NOT NULL
);

Example data

INSERT INTO students (student_id, name, college_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', 101),
(3, 'Charlie', 102),
(4, 'David', 101),
(5, 'Eve', 102),
(6, 'Frank', 103);

INSERT INTO scores (score_id, student_id, subject, score) VALUES
(1, 1, 'Math', 90),
(2, 1, 'Physics', 85),
(3, 2, 'Math', 80),
(4, 2, 'Chemistry', 75),
(5, 3, 'Math', 95),
(6, 3, 'Physics', 88),
(7, 4, 'Math', 85),
(8, 4, 'Physics', 70),
(9, 5, 'Math', 92),
(10, 5, 'Chemistry', 80),
(11, 6, 'Math', 78);

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

Hints

Similar problems

More in: SQL and Databases problems