Fetch top 3 students from each subject using SQL

I have 2 tables.Students and Subjects:

CREATE TABLE Student (
    student_id NUMBER,
    student_name VARCHAR
);

CREATE TABLE Subject (
    subject_id NUMBER,
    student_id NUMBER,
    subject_name VARCHAR,
    marks NUMBER    
);

I have added below data

INSERT INTO Student
VALUES (1001, 'Sanjay Naik');
INSERT INTO Student
VALUES (1002, 'Rohit Sharma');
INSERT INTO Student
VALUES (1003, 'Virat Kolhi');
INSERT INTO Student
VALUES (1004, 'Nitish Kumar Reddy');

INSERT INTO Subject
VALUES (1,1001, 'Maths',72);
INSERT INTO Subject
VALUES (2,1001, 'English',84);
INSERT INTO Subject
VALUES (1,1002, 'Maths',79);
INSERT INTO Subject
VALUES (2,1002, 'English',94);
INSERT INTO Subject
VALUES (1,1003, 'Maths',65);
INSERT INTO Subject
VALUES (2,1003, 'English',89);
INSERT INTO Subject
VALUES (1,1004, 'Maths',54);
INSERT INTO Subject
VALUES (2,1004, 'English',51);

How to fetch top 3 students based on marks obtained in every subject? Like fetch top 3 students in both english and maths subject.

I tried following ,

SELECT * FROM Student
INNER JOIN Subject
ON Student.student_id = Subject.student_id
ORDER BY Subject.marks DESC;

Above query gives students by the order of their marks scored in both subjects. If i add LIMIT 3 it will fetch top 3 whereas i want top 3 students in both english and maths.

Ideally it would be helpful if you could have shared the expected results as well.

You could achieve top 3 students for each subject like below :

1.CTE ranked_students combines the subject name, student name, and marks. RANK() analytic function assigns a rank to each row within each subject_name partition, ordered by marks in descending order. The highest marks get rank 1.

2.rnk <= 3 filters the results to include only the top 3 students for each subject based on their ranks.

Fiddle

WITH ranked_students AS (
    SELECT 
        su.subject_name,
        st.student_name,
        su.marks,
        RANK() OVER (PARTITION BY su.subject_name ORDER BY su.marks DESC) AS rnk
    FROM 
        Subject su
    JOIN 
        Student st ON su.student_id = st.student_id
)
SELECT 
    subject_name,
    student_name,
    marks
FROM 
    ranked_students
WHERE 
    rnk <= 3
ORDER BY 
    subject_name, rnk;

Output