UptoSkills Logo
Login
Rank Student Scores in Each SubjectMedium

Rank Student Scores in Each Subject

MediumSQLPractice library · +20 XPwindow functionssqlranking

A university wants to analyze student performance in different subjects. For an upcoming academic review, they need to rank each student's score in every subject they took. This ranking should consider all students within the same subject, with ties being handled appropriately.

Tables:

  • students (student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL)
  • subjects (subject_id INT PRIMARY KEY, subject_name VARCHAR(50) NOT NULL)
  • scores (score_id INT PRIMARY KEY, student_id INT NOT NULL, subject_id INT NOT NULL, score NUMERIC(5,2), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (subject_id) REFERENCES subjects(subject_id))

Return the student_name, subject_name, and the calculated rank of the score within that subject. The rank should be a whole number, with ties receiving the same rank and the next rank being skipped. Order the results by subject_name and then by rank.

Handle NULL scores by excluding them from ranking. If a subject has no valid scores, it should not appear in the result. If the result set is empty, return an empty set.

Tables

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name VARCHAR(50) NOT NULL
);

CREATE TABLE subjects (
  subject_id INTEGER PRIMARY KEY,
  subject_name VARCHAR(50) NOT NULL
);

CREATE TABLE scores (
  score_id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  subject_id INTEGER NOT NULL,
  score NUMERIC(5,2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

Example data

INSERT INTO students (student_id, student_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

INSERT INTO subjects (subject_id, subject_name) VALUES
(101, 'Mathematics'),
(102, 'Physics'),
(103, 'Chemistry');

INSERT INTO scores (score_id, student_id, subject_id, score) VALUES
(1, 1, 101, 95.50),
(2, 2, 101, 88.00),
(3, 3, 101, 95.50),
(4, 4, 101, 76.00),
(5, 1, 102, 85.00),
(6, 2, 102, 92.50),
(7, 3, 102, 85.00),
(8, 4, 102, 70.00),
(9, 1, 103, 90.00),
(10, 2, 103, 80.00),
(11, 3, 103, 80.00),
(12, 4, 103, 90.00);

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

Hints

Similar problems

More in: SQL and Databases problems