LeetCode 1435 Interview SQL Question with Detailed Explanation | Practice SQL

Previous Video: LeetCode 1421 NPV Queries

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 creating a session bar chart and also includes points to keep in mind to develop SQL queries.

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

[florincopaci]

All your videous are very useful not only this! Keep going! Thank you!

[tejaswaniguttula]

select
'[0-5>' as bin,
sum(case when duration between 0 and 299 then 1 else 0 end) as total
from
sessions

union
select
'[5-10>' as bin,
sum(case when duration between 300 and 599 then 1 else 0 end) as total
from
sessions

union
select
'[10-15>' as bin,
sum(case when duration between 600 and 899 then 1 else 0 end) as total
from
sessions

union
select
'15 or more' as bin,
sum(case when duration >=900 then 1 else 0 end) as total
from
sessions;

[ashmitachakraborty]

Hi, your videos are very helpful, firstly. However, for this query, is it essential to do a second CTE? Isn't just the one enough? I understand that you've done it to ensure that the bin column gets returned in a certain order, but since order wasn't an issue, would leaving that out be a problem? Just ensuring that I get this right.

[pavanch]

I understand this but it's very confusing, it's better with alternative approach.

[siddharthgour]

WITH nt AS (
SELECT session_id, duration / 60 AS duration FROM Sessions
)
SELECT '[0-5>' AS bin, SUM(CASE WHEN nt.duration >= 0 AND nt.duration < 5 THEN 1 ELSE 0 END) AS total FROM nt
UNION ALL
SELECT '[5-10>' AS bin, SUM(CASE WHEN nt.duration >= 5 AND nt.duration < 10 THEN 1 ELSE 0 END) AS total FROM nt
UNION ALL
SELECT '[10-15>' AS bin, SUM(CASE WHEN nt.duration >= 10 AND nt.duration < 15 THEN 1 ELSE 0 END) AS total FROM nt
UNION ALL
SELECT '15 or more' AS bin, SUM(CASE WHEN nt.duration >= 15 THEN 1 ELSE 0 END) AS total FROM nt;

why the above code is not working?

[ManasNandMohan]

with cte as
(SELECT *, CASE WHEN duration BETWEEN 0 AND 299 THEN '[O-5>'
WHEN duration BETWEEN 300 AND 599 THEN '[5-10>'
WHEN duration BETWEEN 600 AND 899 THEN '[10-15>'
ELSE '15 or more' END AS bin
FROM Sessions),


cte2 as(
SELECT '[O-5>' as bin
UNION
SELECT'[5-10>'
UNION
SELECT'[10-15>'
UNION
SELECT '15 or more' )

select cte2.bin, coalesce(count(cte.bin), 0)as total
From cte2
left join cte on cte2.bin=cte.bin
group by cte2.bin
order by
CASE
WHEN cte2.bin = '[0-5>' THEN 1
WHEN cte2.bin = '[5-10>' THEN 2
WHEN cte2.bin = '[10-15>' THEN 3
WHEN cte2.bin= '15 or more' THEN 4 end

[yashmistry]

Hello,
The explanation was amazing!!
I have few queries regarding my career is there any way I could contact you?

[reetika_]

can anyone please tell me why this is wrong :

With cte1 as
(SELECT *, CASE
WHEN duration BETWEEN 0 AND 299 THEN '[0-5>'
WHEN duration BETWEEN 300 AND 599 THEN '[5-10>'
WHEN duration BETWEEN 600 AND 899 THEN '[5-10>'
else '15 or more'
END AS bin1
FROM sessions),

cte2 as
(SELECT '[0-5>' AS bin2
UNION
SELECT '[5-10>'
UNION
SELECT '[10-15>'
UNION
SELECT '15 or more'),

cte3 as
(SELECT cte2.bin2 as bin, CASE
WHEN cte1.session_id IS NOT NULL THEN COUNT(cte1.session_id)
ELSE 0
END AS cnt
FROM cte1
RIGHT JOIN cte2
ON cte1.bin1 = cte2.bin2
GROUP BY cte2.bin2, cte1.session_id )

select cte3.bin, sum(cte3.cnt)as total
from cte3
group by cte3.bin

[ashmitachakraborty]

Hi, your videos are very helpful, firstly. However, for this query, is it essential to do a second CTE? Isn't just the one enough? I understand that you've done it to ensure that the bin column gets returned in a certain order, but since order wasn't an issue, would leaving that out be a problem? Just ensuring that I get this right.