Tricky SQL Screening Question | Applicants Failing to Get the Query Correct

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');

[@shubhjais8753]

I tried with partition by: select subject, id, marks, max(marks) over(partition by subject order by subject) as max_marks, count(subject) over(partition by subject order by subject) as participents from score ▶ #i used table name as score

[@ericaleverson9430]

I like the Windows function option. Less code and more readable.

[@blinquedu2]

hi thanks for the video... I just come up with a new ideia of query using correlated sub-query + cte applying inner join :with student as ( select subject_name, count(*) as participants from students group by subject_name ) select a.subject_name, a.id, a.marks, (select max(marks) from students b where b.subject_name = a.subject_name) as max_marks, c.participants from students a join student c on c.subject_name = a.subject_name

[@absentdata]

That is a great solution!

[@nigereast3796]

I think from a BI perspective , The last query is optimal

[@amazingriknow5986]

Hi, Kindly include table creation and value insertion script in description

[@absentdata]

Added Table Insertion Script.

[@amazingriknow5986]

@@absentdata thank you

[@yusufbas035]

hi thank you for your videos I have been watching your videos for 9 months it helps a lot.

[@absentdata]

Happy to help!

[@user-wx3dn3il2f]

Thnk you! first i've thoght that choice 3 you are using. but i need to always tweak the way of querying this data! thank you for your information with different point of view!

[@absentdata]

Glad you good a new viewpoint. Share the video went anyone you think it will help.

[@XxR0cAF3llaxX]

What was the actual question? We have the Table along with the Result Table that they wanted the applicants to produce, by looking at the result Table i have no idea what the implied question would be. I would have been screened out real quick lol

[@method341]

What is id here? The primary key? The student id? The subject id? I'm confused 😂

[@absentdata]

yes you can treat the ID as a unique identifier