Thanks for this playlist. This is really helpful Below is my approach WITH CTE AS ( SELECT DISTINCT sub_id from submissions where parent_id IS NULL) SELECT cte.sub_id AS post_id, COUNT(DISTINCT sb.sub_id) AS no_of_comments from CTE cte LEFT JOIN submissions sb ON cte.sub_id=sb.parent_id GROUP BY cte.sub_id
this is a lot clearer, getting all posts in one cte and then doing a left join for posts and getting count of distinct comments with group by instead of making a cte of posts and a cte of comments counts per parent and doing a left join both cte and then using suing coalese/case
Will this work? SELECT s.sub_id AS post_id, COUNT(DISTINCT p.sub_id) AS number_of_comments FROM (SELECT * from Submissions WHERE parent_id IS NULL) as s LEFT JOIN Submissions p ON s.sub_id = p.parent_id GROUP BY s.sub_id;
WITH cte1 AS(SELECT DISTINCT sub_id ' FROM Submissions WHERE parent_id IS NULL) WITH cte2(SELECT parent_id, COUNT(DISTINCT sub_id) AS num FROM submissions WHERE parent_id IS NOT NULL GROUP BY parent_id) SELECT sub_id AS post_id ,CASE WHEN num IS NOT NULL THEN num ELSE 0 END AS number_of_comments FROM cte1 LEFT JOIN cte2 ON cte1.sub_id=cte2.parent_id ORDER BY post_id
This was a good question. Confusing question at first, you explained it very clearly.
Glad that this video was useful, Sigangsa.
Good stuff. Thank you for the explanation. Definitely looking forward to more content.
Glad you found it useful, Armando.
A new way of explanning by giving visualization of each statement , please keep it for , It make it more clear
I like the fact that these solutions are based on CTEs, thanks!
Glad that you found the video useful 😊
@@EverydayDataScience Please create easy algorithm problems from LC in Python as well.
Awesome,clear
nice explanation...Thanks
Glad that you found it useful.
19th one
SELECT sub_id, count(*)
FROM Submissions
GROUP BY sub_id
HAVING DISTINCT parent_id IS NOT NULL
ORDER BY sub_id;
why will this not work?
Vo 7 bhi to post h sir vo kyo nahi aya output me
Great stuff...
how about using coalesce(num,0) as number_of_comments
rather than case statement
Would do the job!
Premium on LeetCode
Thanks for this playlist. This is really helpful
Below is my approach
WITH CTE AS (
SELECT DISTINCT sub_id from submissions where parent_id IS NULL)
SELECT cte.sub_id AS post_id, COUNT(DISTINCT sb.sub_id) AS no_of_comments from CTE cte
LEFT JOIN submissions sb ON cte.sub_id=sb.parent_id
GROUP BY cte.sub_id
this is a lot clearer, getting all posts in one cte and then doing a left join for posts and getting count of distinct comments with group by instead of making a cte of posts and a cte of comments counts per parent and doing a left join both cte and then using suing coalese/case
Will this work?
SELECT s.sub_id AS post_id, COUNT(DISTINCT p.sub_id) AS number_of_comments FROM
(SELECT * from Submissions
WHERE parent_id IS NULL) as s
LEFT JOIN Submissions p
ON s.sub_id = p.parent_id
GROUP BY s.sub_id;
WITH cte1 AS(SELECT DISTINCT sub_id '
FROM Submissions
WHERE parent_id IS NULL)
WITH cte2(SELECT parent_id, COUNT(DISTINCT sub_id) AS num
FROM submissions
WHERE parent_id IS NOT NULL
GROUP BY parent_id)
SELECT sub_id AS post_id ,CASE WHEN num IS NOT NULL THEN num ELSE
0 END AS number_of_comments
FROM cte1
LEFT JOIN cte2
ON cte1.sub_id=cte2.parent_id
ORDER BY post_id