UptoSkills Logo
Login
Student Course Ranking by SemesterMedium

Student Course Ranking by Semester

MediumSQLPractice library · +20 XPwindow functionssqlranking

UptoSkills Arena wants to rank students in each course they've taken based on their scores within that specific course.

Tables:

  • students: student_id (PK), student_name
  • courses: course_id (PK), course_name
  • enrollments: enrollment_id (PK), student_id (FK), course_id (FK), semester, score (integer from 0 to 100)

Return the student_name, course_name, semester, score, and the student's rank in that course for that semester. The rank should be 1 for the highest score. Ties should receive the same rank, with the next rank being skipped (e.g., 1, 1, 3).

Order the results by semester, then course_name, then score in descending order.

Tables

CREATE TABLE students (
  student_id integer PRIMARY KEY,
  student_name varchar(100) NOT NULL
);
CREATE TABLE courses (
  course_id integer PRIMARY KEY,
  course_name varchar(100) NOT NULL
);
CREATE TABLE enrollments (
  enrollment_id integer PRIMARY KEY,
  student_id integer NOT NULL,
  course_id integer NOT NULL,
  semester varchar(20) NOT NULL,
  score integer CHECK (score >= 0 AND score <= 100),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Example data

INSERT INTO students (student_id, student_name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David');
INSERT INTO courses (course_id, course_name) VALUES (101, 'Math'), (102, 'Physics'), (103, 'Chemistry');
INSERT INTO enrollments (enrollment_id, student_id, course_id, semester, score) VALUES 
(1, 1, 101, 'Fall 2023', 95), (2, 2, 101, 'Fall 2023', 90), (3, 3, 101, 'Fall 2023', 90), (4, 4, 101, 'Fall 2023', 85), 
(5, 1, 102, 'Fall 2023', 88), (6, 2, 102, 'Fall 2023', 92), (7, 3, 102, 'Fall 2023', 88), 
(8, 1, 101, 'Spring 2024', 92), (9, 2, 101, 'Spring 2024', 98), (10, 3, 101, 'Spring 2024', 92);

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

Hints

Similar problems

More in: SQL and Databases problems