This is a real SQL interview screening question for a medium size marketing firm that was trying get a specific answer from interviewees. I provide 3 ways to answer the pretty easy question. The tricky part would be not using a join to answer the question. This was also posted on Reddit and got some interesting feedback

0:20 Reddit Post
0:38 Interview Question
1:37 Writing the Queries
2:40 Inner Join Method
5:33 CTE Method
7:00 Window Function Method

You can recreate the table with this:

CREATE TABLE grades (
`Id` INTEGER,
`name` VARCHAR(5),
`subject_name` VARCHAR(9),
`marks` INTEGER
);

INSERT INTO grades
(`Id`, `name`, `subject_name`, `marks`)
VALUES
('1', 'Rob', 'Maths', '30'),
('2', 'White', 'English', '50'),
('3', 'Mark', 'Maths', '70'),
('4', 'David', 'English', '40'),
('5', 'Smith', 'Chemistry', '80'),
('6', 'Greg', 'Maths', '20'),
('7', 'Will', 'English', '45');