LeetCode 1280 Interview SQL Question with Detailed Explanation | Practice SQL

[@rb4754]

I was pleasantly surprised to find such valuable content on this channel, even though it currently has only 3.2k subscribers. The way the problem was explained step by step was truly exceptional, and I have watched many videos on this topic before, but none of them were as clear and thorough as yours. Your ability to pass on knowledge efficiently is commendable, and I truly appreciate the effort you put into making such educational videos. I strongly encourage you to continue producing content with the same patience and dedication. I have no doubt that if you keep creating videos of this quality, your channel will grow rapidly and attract a much larger audience. Not everyone possesses the skill to teach complex concepts effectively, but you certainly have a talent for it.Your hard work and commitment to providing valuable educational resources deserve recognition, and I believe you will achieve great success in the future. Keep up the excellent work, and thank you so much for sharing your knowledge.

[@helencats-jwl]

very clear and easy to understand, thank you so much for doing this video. I thought this one is a hard one though it is categorized in easy😂 your logic makes sense to me

[@EverydayDataScience]

Glad you found it helpful, Helen.

[@professorpoke]

It indeed is hard. Definitely not an easy one.

[@AKASHKUMAR-bf7co]

this is video is gem for me, thank you

[@EverydayDataScience]

Glad that you found the video useful 😊

[@Mech2ML]

seriously this is a real quality content thanks a lot for helping

[@abhisheksinghyadav4970]

Thanks man I dont usually comment on videos. but this solution is beautifully explained as well as others. Keep posting you'll grow alot for sure.

[@ambeshpandey8937]

one thing i can say best way anyone has explained

[@EverydayDataScience]

Thanks for such kind words 😊 Glad that you found the video useful.

[@ayushvats4380]

Great explanation , no way this was categorized as easy xD

[@clouddrive6833]

That's Great Here is an alternate way : # Write your MySQL query statement below WITH cte1 AS ( SELECT * FROM students CROSS JOIN subjects ), cte2 AS ( SELECT student_id, subject_name, COUNT(subject_name) AS val FROM examinations GROUP BY student_id, subject_name ) SELECT cte1.*, COALESCE(cte2.val, 0) AS attended_exams FROM cte1 LEFT JOIN cte2 ON cte1.student_id = cte2.student_id AND cte1.subject_name = cte2.subject_name order by student_id,subject_name;

[@kavyabanka4482]

WOW THIS IS FABULOUS EXPLANATION

[@sinchanagowda8302]

Very clear and insightful, Thanks for the video!

[@EverydayDataScience]

Glad that you found the video useful 😊

[@aaravkumarsingh4018]

explained in very easy and detail way

[@EverydayDataScience]

Glad that you found it useful, Aarav.

[@ashishgupta-bw8pt]

great videos. keep it up. bro.

[@patiladiti2116]

Solution w/o common table: SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.subject_name) as attended_exams FROM Students AS s CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name GROUP BY s.student_id, sub.subject_name ORDER BY s.student_id, sub.subject_name;

[@sauravchandra10]

Good question in terms of the variety of information. How can we do a cross join in pandas?

[@VishnuvardhanJadava]

great video. I think we can simply use ifnull instead of case.

[@alfabinomial6183]

YOU ARE AWESOMEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE!!!!!!!!!!

[@taylorkaser7529]

thank you this was great :)

[@GauravSubodh]

Amazing Explination

[@EverydayDataScience]

Glad that you found the video useful 😊

[@fighterdse3589]

Very clear!!!🥳🥳

[@neelshah8247]

If you're using SQL Server, you can use the ISNULL() Function instead of the CASE Statements.

[@continnum_radhe-radhe]

❤❤❤

[@EverydayDataScience]

Glad that you found the video useful 😊

[@irislynn4970]

thank you , appreciate this

[@EverydayDataScience]

Glad you liked it, Iris.

[@saisushanthdurvasulabitsian]

as usual excellent explanation , thank you , here is alternate way with one cte less, pls review and let me know: with cte(student_id,student_name,subject_name) as ( select stu.student_id , stu.student_name , sub.subject_name from students stu, subjects sub ) select c.student_id , c.student_name , c.subject_name , sum(case when (ex.student_id is null or ex.subject_name is null) then 0 else 1 end) as attended_exams from cte c left outer join examinations ex on (c.student_id = ex.student_id and c.subject_name = ex.subject_name) group by c.student_id,c.subject_name order by c.student_id,c.subject_name

[@aniketyadav343]

One of the longest query for sure

[@mlvprasadofficial]

23

[@yassinbenyahia6801]

this is not an easy problem it should be medium

[@containthis4450]

this is suppose to be an easy question.. I think there should be an easier solution because you lost me in the first 3 minutes

[@containthis4450]

select a.student_id, a.student_name, b.subject_name, count(c.subject_name) as attended_exams from Students as a join Subjects as b left join Examinations as c on a.student_id = c.student_id and b.subject_name = c.subject_name group by a.student_id, b.subject_name ORDER BY a.student_id, b.subject_name;

[@EverydayDataScience]

Definitely, there can be many easier solutions to this. I’ll try to keep this mind going forward , thanks for pointing it out 😊

[@shashankshekhar8186]

Glad someone spoked

[@rb4754]

@@EverydayDataScience No you did right thing. There could be more simpleistic ways. However, with this one explaination I understood many concepts such as case, cross join, CTE. That is an awesome explanation. Keep up this. Thank you.

[@ashishsuman7079]

A bit more time taking but easier approach ..for beginners. SELECT Students.student_id, Students.student_name, Subjects.subject_name, COUNT(Examinations.subject_name) AS attended_exams FROM Students CROSS JOIN Subjects LEFT JOIN Examinations ON Students.student_id = Examinations.student_id AND Subjects.subject_name = Examinations.subject_name GROUP BY student_name, subject_name ORDER BY student_id, subject_name

[@firzainsanudzaky3763]

do you think if i was an intern should i use edds method or your method ?

[@antonigolos1406]

@@firzainsanudzaky3763 I am pretty sure that this method was the intendent one. I would rather avoid using cte if you do not need to

[@ashmitachakraborty6637]

Hey, thank you for the video. However, when I tried, only two test cases passed out of 14. Following is the code I did, With cte1 as ( Select * from Students Cross join Subjects ), /* 1 Alice Math 1 Alice Physics*/ cte2 as ( Select student_id, subject_name, count(subject_name) as count From Examinations Group by student_id, subject_name ) /* 1 Math 3 1 Physics 2 */ Select cte1.student_id, cte1.student_name, cte1.subject_name, case when count is not null then count else 0 end as attended_exams From cte1 LEFT JOIN cte2 ON cte1.student_id = cte2.student_id #1 Alice Math and cte1.subject_name and cte2.subject_name #1 Alice Math 3 ORDER By cte1.student_id, cte1.subject_name Could you kindly point me in the right direction for this? The count function is not working right for me at all. It's coming as zero. Any help is appreciated.

[@EverydayDataScience]

Try renaming count(subject_name) to something else, not count and then use it.

[@ashmitachakraborty6637]

@@EverydayDataScience still the same

[@ashmitachakraborty6637]

I had put an 'and' in place of =. Thanks for responding.