LeetCode 1280 Interview SQL Question with Detailed Explanation | Practice SQL

Previous Video: LeetCode 1294 Employee Team Size
• LeetCode 1303 Intervie...
In this video I solve and explain a leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question is about students and examinations and also includes points to keep in mind to develop SQL queries. You will also learn about COMMON TABLE EXPRESSIONS.
LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.
If you found this helpful, Like and Subscribe to the channel for more content.
#LeetCodeSQL #FAANG #SQLinterviewQuestions

[@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.

[@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.

[@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

[@Mech2ML]

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

[@ayushvats4380]

Great explanation , no way this was categorized as easy xD

[@ambeshpandey8937]

one thing i can say best way anyone has explained

[@EverydayDataScience]

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

[@sauravchandra10]

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

[@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.

[@VishnuvardhanJadava]

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

[@alfabinomial6183]

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

[@ashishgupta-bw8pt]

great videos. keep it up. bro.

[@GauravSubodh]

Amazing Explination

[@EverydayDataScience]

Glad that you found the video useful 😊

[@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;

[@taylorkaser7529]

thank you this was great :)

[@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 😊

[@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

[@irislynn4970]

thank you , appreciate this

[@EverydayDataScience]

Glad you liked it, Iris.

[@aniketyadav343]

One of the longest query for sure

[@mlvprasadofficial]

23

[@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.

[@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.

[@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