Thank you so much Frederik!! I was having a really bad time with this problem, seriously!, I tried with self joins then with functions, then with self joins again haha, and thanks to you I realized I wasn't coding the right way my ON conditions. Thank you again! I'll watch all your videos, greetings from Chile
Hi Nicole I didn;t understand the below, please help me explaining this (s1.id = s2.id -1 and s1.id = s3.id -2) OR (s1.id = s2.id +1 and s1.id = s3.id -1) OR (s1.id = s2.id +1 and s1.id = s3.id +2) Unable to picture
Thanks! I'm trying to remember my own problems when I first solved these questions when explaining them to others. I want everyone to be able to follow along!
When consecutive rows are more than any number, the solution generalizes like this: WITH bigger_than_100 AS (SELECT *, id - ROW_NUMBER() OVER(ORDER BY visit_date) AS 'cons_tag' FROM stadium WHERE people >= 100) SELECT id, visit_date, people FROM bigger_than_100 WHERE cons_tag IN (SELECT cons_tag FROM bigger_than_100 GROUP BY cons_tag HAVING COUNT(id) >= 3)
Thank you for this video it help me think on how to solve using other means like lag() and Lead() to look at the previous row and next row. I'm able to do the correct code and was accepted as well.
Just in case someone want to know other way to solve this problem below is my code. -- FILTER ONLY MORE THAN OR EQUAL 100 WITH CTE1 AS ( SELECT * FROM STADIUM WHERE PEOPLE >=100 ORDER BY ID ) , -- GET MAXIMUM AND MININUM NUMBER CTE2 AS ( SELECT MIN(ID) MINX,MAX(ID) MAXX FROM CTE1 ) , -- ATTACHED THE MAX AND MIN CTE3 AS ( SELECT * FROM CTE1 INNER JOIN CTE2 ON 1=1 ) , CTE4 AS ( SELECT *, -- WHEN IT IS THE FIRST RECORD THEN P1 = 0 ELSE GET DIFF OF ID WITH ABOVE ROW CASE WHEN MINX = ID THEN 0 ELSE ID - LAG(ID) OVER (ORDER BY ID) END P1, -- WHEN IT IS THE FIRST RECORD THEN P2 =0 ELSE GET THE DIFF OF WITH ABOVE 2 ROW CASE WHEN MINX = ID THEN 0 ELSE COALESCE(LAG(ID) OVER (ORDER BY ID) - LAG(ID,2) OVER (ORDER BY ID),0) END P2, -- WHEN IT IS THE LAST RECORD THEN L1 = 0 ELSE GET THE DIFF OF ID WITH BELOW ROW CASE WHEN MAXX = ID THEN 0 ELSE LEAD(ID) OVER (ORDER BY ID) - ID END L1, -- WHEN IS THE LAST RECORD THEN L2 = 0 ELSE GET THE DIFF OF ID WITH BELOW 2 ROW CASE WHEN MAXX = ID THEN 0 WHEN MAXX -1 = ID THEN ID - LAG(ID) OVER (ORDER BY ID) ELSE LEAD(ID,2) OVER (ORDER BY ID) - LEAD(ID,1) OVER (ORDER BY ID) END L2 FROM CTE3 ) , CTE5 AS ( SELECT *, -- CHECK DIFF IF 1 DAY THEN PASS FOR THE 3 POSSIBLE COMBINATION CASE WHEN (L1=1 AND L2=1) OR (P1=1 AND P2=1) OR (L1=1 AND P1=1) THEN 'PASS' ELSE 'FAILED' END STATUS FROM CTE4 ) SELECT ID,VISIT_DATE,PEOPLE FROM CTE5 WHERE STATUS ='PASS';
at 8:38, he said due to overlapping triplets was the reason the result had duplicates. How does it work? In my mind, the sql query goes one row at a time then check if any of the three condition is true then add it to the result. How can there be duplicates? It is like three for loops with s1, s2, s3 and you only loop through s1 once on the outer for loop. Thanks.
Thanks @Frederik Müller for nice explanation. I tried with lead and self join approach. I formed my SQL using self join little bit different. Below is the self join in TSQL dialect. select t1.id, t1.visit_date, t1.people from dbo.Stadium t1 left join dbo.Stadium t2 on t1.id = t2.id-1 left join dbo.Stadium t3 on t1.id = t3.id-2 where (t1.people > = 100 and t2.people > = 100 and t3.people > = 100) OR (t1.people > = 100 and t2.people >= 100 and coalesce(t3.people,-100) = -100) OR (t1.people > = 100 and coalesce(t2.people,-100) = -100 and coalesce(t3.people,-100) = -100) order by t1.visit_date What is your view points on this above approach?
Hi Fred I didn;t understand the below, please help me explaining this (s1.id = s2.id -1 and s1.id = s3.id -2) OR (s1.id = s2.id +1 and s1.id = s3.id -1) OR (s1.id = s2.id +1 and s1.id = s3.id +2) Unable to picture
So there are three possibilities as we are selecting S1 finally, S1.id can be first, middle or last to be consecutive right? so we have to say that any1 condition should match thats why this syntax (s1.id = s2.id -1 and s1.id = s3.id -2) this for s1.id at last position /// (s1.id = s2.id +1 and s1.id = s3.id -1) this for s1.id at middle and (s1.id = s2.id +1 and s1.id = s3.id +2) this for s1.id for first position. Hope you understand!
this part please can ya explain please!!!!!!!!!!!!!!!!!! (s1.id = s2.id -1 and s1.id = s3.id -2) OR (s1.id = s2.id +1 and s1.id = s3.id -1) OR (s1.id = s2.id +1 and s1.id = s3.id +2)
So there are three possibilities as we are selecting S1 finally, S1.id can be first, middle or last to be consecutive right? so we have to say that any1 condition should match thats why this syntax (s1.id = s2.id -1 and s1.id = s3.id -2) this for s1.id at last position /// (s1.id = s2.id +1 and s1.id = s3.id -1) this for s1.id at middle and (s1.id = s2.id +1 and s1.id = s3.id +2) this for s1.id for first position. Hope you understand!
![LeetCode 620: Not Boring Movies [SQL]](http://i.ytimg.com/vi/8d3FBUpSZeI/mqdefault.jpg)
![LeetCode 620: Not Boring Movies [SQL]](/img/tr.png)
![LeetCode 262: Trips and Users [SQL]](http://i.ytimg.com/vi/jm0LwWWPoyU/mqdefault.jpg)
![LeetCode 185: Department Top Three Salaries [SQL]](http://i.ytimg.com/vi/OsMuAspJAkc/mqdefault.jpg)
![เขามัทรี - เอ็กซ์ ศุภกฤต - จอนนี่มิวสิค [ Official MV ]](http://i.ytimg.com/vi/AS9ufKuQIVs/mqdefault.jpg)
You are the Nick White of Leetcode Database explanation.
Hi Frederik , You made this problem look so simple. Love from India.
Awesome video, really help me understand the logic of why we have multiple or clauses. Thank You
Thank you so much Frederik!! I was having a really bad time with this problem, seriously!, I tried with self joins then with functions, then with self joins again haha, and thanks to you I realized I wasn't coding the right way my ON conditions. Thank you again! I'll watch all your videos, greetings from Chile
Thank you for your support, Nicole! I'm glad the video was helpful for you. My former manager is from Chile, as well. What a small world 🙂
Hi Nicole
I didn;t understand the below, please help me explaining this
(s1.id = s2.id -1 and s1.id = s3.id -2) OR
(s1.id = s2.id +1 and s1.id = s3.id -1) OR
(s1.id = s2.id +1 and s1.id = s3.id +2)
Unable to picture
Your breakdowns are so easy to undertand!
Thanks! I'm trying to remember my own problems when I first solved these questions when explaining them to others. I want everyone to be able to follow along!
@@frederikmuller Totally agreed !!!
thnks a lot frederik , your explanation is very easy to grasp😃
When consecutive rows are more than any number, the solution generalizes like this:
WITH bigger_than_100 AS
(SELECT
*,
id - ROW_NUMBER() OVER(ORDER BY visit_date) AS 'cons_tag'
FROM stadium
WHERE people >= 100)
SELECT id, visit_date, people
FROM bigger_than_100
WHERE cons_tag IN
(SELECT cons_tag
FROM bigger_than_100
GROUP BY cons_tag
HAVING COUNT(id) >= 3)
Thank you for this video it help me think on how to solve using other means like lag() and Lead() to look at the previous row and next row. I'm able to do the correct code and was accepted as well.
Just in case someone want to know other way to solve this problem below is my code.
-- FILTER ONLY MORE THAN OR EQUAL 100
WITH CTE1 AS (
SELECT * FROM STADIUM WHERE PEOPLE >=100 ORDER BY ID
)
,
-- GET MAXIMUM AND MININUM NUMBER
CTE2 AS (
SELECT MIN(ID) MINX,MAX(ID) MAXX FROM CTE1
)
,
-- ATTACHED THE MAX AND MIN
CTE3 AS (
SELECT *
FROM CTE1 INNER JOIN CTE2 ON
1=1
)
,
CTE4 AS (
SELECT *,
-- WHEN IT IS THE FIRST RECORD THEN P1 = 0 ELSE GET DIFF OF ID WITH ABOVE ROW
CASE WHEN MINX = ID THEN 0 ELSE ID - LAG(ID) OVER (ORDER BY ID) END P1,
-- WHEN IT IS THE FIRST RECORD THEN P2 =0 ELSE GET THE DIFF OF WITH ABOVE 2 ROW
CASE WHEN MINX = ID THEN 0
ELSE COALESCE(LAG(ID) OVER (ORDER BY ID) - LAG(ID,2) OVER (ORDER BY ID),0) END P2,
-- WHEN IT IS THE LAST RECORD THEN L1 = 0 ELSE GET THE DIFF OF ID WITH BELOW ROW
CASE WHEN MAXX = ID THEN 0
ELSE LEAD(ID) OVER (ORDER BY ID) - ID
END L1,
-- WHEN IS THE LAST RECORD THEN L2 = 0 ELSE GET THE DIFF OF ID WITH BELOW 2 ROW
CASE WHEN MAXX = ID THEN 0
WHEN MAXX -1 = ID THEN ID - LAG(ID) OVER (ORDER BY ID)
ELSE LEAD(ID,2) OVER (ORDER BY ID) - LEAD(ID,1) OVER (ORDER BY ID)
END L2
FROM CTE3
)
,
CTE5 AS (
SELECT *,
-- CHECK DIFF IF 1 DAY THEN PASS FOR THE 3 POSSIBLE COMBINATION
CASE WHEN (L1=1 AND L2=1) OR (P1=1 AND P2=1) OR (L1=1 AND P1=1)
THEN 'PASS'
ELSE 'FAILED'
END STATUS
FROM CTE4
)
SELECT ID,VISIT_DATE,PEOPLE FROM CTE5 WHERE STATUS ='PASS';
at 8:38, he said due to overlapping triplets was the reason the result had duplicates. How does it work? In my mind, the sql query goes one row at a time then check if any of the three condition is true then add it to the result. How can there be duplicates? It is like three for loops with s1, s2, s3 and you only loop through s1 once on the outer for loop. Thanks.
Thanks @Frederik Müller for nice explanation. I tried with lead and self join approach. I formed my SQL using self join little bit different. Below is the self join in TSQL dialect.
select
t1.id,
t1.visit_date,
t1.people
from
dbo.Stadium t1
left join
dbo.Stadium t2
on
t1.id = t2.id-1
left join
dbo.Stadium t3
on
t1.id = t3.id-2
where
(t1.people > = 100 and t2.people > = 100 and t3.people > = 100) OR
(t1.people > = 100 and t2.people >= 100 and coalesce(t3.people,-100) = -100) OR
(t1.people > = 100 and coalesce(t2.people,-100) = -100 and coalesce(t3.people,-100) = -100)
order by t1.visit_date
What is your view points on this above approach?
could you please solve this problem by using Window function?
Thx. It helps me there.
Great video!!!!
Hi Fred
I didn;t understand the below, please help me explaining this
(s1.id = s2.id -1 and s1.id = s3.id -2) OR
(s1.id = s2.id +1 and s1.id = s3.id -1) OR
(s1.id = s2.id +1 and s1.id = s3.id +2)
Unable to picture
enumerate s1.id in three positions of three consecutive ids
So there are three possibilities as we are selecting S1 finally, S1.id can be first, middle or last to be consecutive right? so we have to say that any1 condition should match thats why this syntax (s1.id = s2.id -1 and s1.id = s3.id -2) this for s1.id at last position /// (s1.id = s2.id +1 and s1.id = s3.id -1) this for s1.id at middle and (s1.id = s2.id +1 and s1.id = s3.id +2) this for s1.id for first position. Hope you understand!
What if there are 1000 consecutive rows. this solution works only when you know the outputs before you try. Just my opinion.
Why wouldn’t it work for 1000 rows?
you look like David J Malan and Jared Dunn from Silicon Valley, combined.
lol
this part please can ya explain please!!!!!!!!!!!!!!!!!!
(s1.id = s2.id -1 and s1.id = s3.id -2) OR
(s1.id = s2.id +1 and s1.id = s3.id -1) OR
(s1.id = s2.id +1 and s1.id = s3.id +2)
So there are three possibilities as we are selecting S1 finally, S1.id can be first, middle or last to be consecutive right? so we have to say that any1 condition should match thats why this syntax (s1.id = s2.id -1 and s1.id = s3.id -2) this for s1.id at last position /// (s1.id = s2.id +1 and s1.id = s3.id -1) this for s1.id at middle and (s1.id = s2.id +1 and s1.id = s3.id +2) this for s1.id for first position. Hope you understand!