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.
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
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;
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;
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
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 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.
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
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.
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.
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
Glad you found it helpful, Helen.
It indeed is hard. Definitely not an easy one.
seriously this is a real quality content thanks a lot for helping
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;
WOW THIS IS FABULOUS EXPLANATION
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.
one thing i can say best way anyone has explained
Thanks for such kind words 😊 Glad that you found the video useful.
explained in very easy and detail way
Glad that you found it useful, Aarav.
Great explanation , no way this was categorized as easy xD
Very clear and insightful, Thanks for the video!
Glad that you found the video useful 😊
this is video is gem for me, thank you
Glad that you found the video useful 😊
great videos. keep it up. bro.
YOU ARE AWESOMEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE!!!!!!!!!!
great video. I think we can simply use ifnull instead of case.
Good question in terms of the variety of information. How can we do a cross join in pandas?
Amazing Explination
Glad that you found the video useful 😊
Very clear!!!🥳🥳
thank you this was great :)
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;
If you're using SQL Server, you can use the ISNULL() Function instead of the CASE Statements.
thank you , appreciate this
Glad you liked it, Iris.
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
One of the longest query for sure
❤❤❤
Glad that you found the video useful 😊
23
this is not an easy problem it should be medium
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
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;
Definitely, there can be many easier solutions to this. I’ll try to keep this mind going forward , thanks for pointing it out 😊
Glad someone spoked
@@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.
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
do you think if i was an intern should i use edds method or your method ?
@@firzainsanudzaky3763 I am pretty sure that this method was the intendent one. I would rather avoid using cte if you do not need to
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.
Try renaming count(subject_name) to something else, not count and then use it.
@@EverydayDataScience still the same
I had put an 'and' in place of =. Thanks for responding.