CREATE TABLE Courses (
    CourseID INTEGER PRIMARY KEY,
    CourseTitle VARCHAR(255)
);

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(255)
);

CREATE TABLE Enrollments (
    EnrollmentID INTEGER PRIMARY KEY,
    StudentID INTEGER,
    CourseID INTEGER,
    Semester VARCHAR(10),
    Grade INTEGER
);

INSERT INTO Courses (CourseID, CourseTitle) VALUES
(1, 'Web Development'),
(2, 'Introduction to Databases'),
(3, 'Advanced Algorithms'),
(4, 'Machine Learning');

INSERT INTO Students (StudentID, StudentName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Semester, Grade) VALUES
(1, 1, 1, 'Spring 2024', 85),
(2, 1, 2, 'Fall 2023', 90),
(3, 2, 3, 'Spring 2024', 80),
(4, 2, 4, 'Fall 2023', 70),
(5, 3, 4, 'Spring 2024', 95),
(6, 3, 1, 'Fall 2023', 75);


WITH RankedEnrollments AS (
    SELECT
        e.StudentID,
        s.StudentName,
        c.CourseTitle,
        e.Semester,
        e.Grade,
        ROW_NUMBER() OVER (
            PARTITION BY e.StudentID 
            ORDER BY e.Semester DESC, e.Grade DESC
        ) AS Rank
    FROM Enrollments e
    JOIN Students s ON e.StudentID = s.StudentID
    JOIN Courses c ON e.CourseID = c.CourseID
)

-- Output the ranked enrollments
SELECT
    StudentID,
    StudentName,
    CourseTitle,
    Semester,
    Grade,
    Rank
FROM RankedEnrollments
ORDER BY StudentID, Rank;