Thanks for the logical solution . Achived the same using lead lag function with CTE AS ( SELECT *, lead(no_of_people,1) over(order by visit_date) AS lead_1st_day, lead(no_of_people,2) over(order by visit_date) AS lead_2nd_day, lag(no_of_people,1) over(order by visit_date) AS lag_1st_day, lag(no_of_people,2) over(order by visit_date) AS lag_2nd_day FROM STADIUM ) SELECT id,visit_date,no_of_people FROM cte WHERE no_of_people>=100 and ((lead_1st_day>=100 AND lead_2nd_day>=100) OR (lag_1st_day>=100 and lag_2nd_day>=100))
@@ankitbansal6 hi. this wont work for days where no_of_people>=100 which only have lead_1st_day>100 and lag_1st_day>=100 but lead_2nd_day and lag_2nd_day less than 100. correct solution- with cte as (select *, lead(no_of_people,1,0) over (order by id) as lead1 , lead(no_of_people,2,0) over (order by id) as lead2, lag(no_of_people,1,0) over (order by id) as lag1, lag(no_of_people,2,0) over (order by id) as lag2 from stadium) , cte2 as ( select * , case when lag1
@@anubhavsahay8116 no need of one more of cte we can add one extra condition to Punit code...i.e with CTE AS ( SELECT *, lead(no_of_people,1) over(order by visit_date) AS lead_1st_day, lead(no_of_people,2) over(order by visit_date) AS lead_2nd_day, lag(no_of_people,1) over(order by visit_date) AS lag_1st_day, lag(no_of_people,2) over(order by visit_date) AS lag_2nd_day FROM STADIUM ) --select * from cte SELECT id,visit_date,no_of_people FROM cte WHERE no_of_people>=100 and ((lead_1st_day>=100 AND lead_2nd_day>=100) OR (lag_1st_day>=100 and lag_2nd_day>=100) or (lag_1st_day>=100 and lead_1st_day>=100)) let us know if this wont work
Excellent Approach Ankit . Need to practise to adapt the thinking approach. I was used the legacy thinking approach : with cte1 as (Select * ,lag(no_of_people,1,0) over ( order by visit_date ) as prev_one_day_cnt ,lag(no_of_people,2,0) over ( order by visit_date ) as prev_two_day_cnt ,lead(no_of_people,1,0) over ( order by visit_date ) as next_one_day_cnt ,lead(no_of_people,2,0) over ( order by visit_date ) as next_two_day_cnt from stadium) Select visit_date,no_of_people from cte1 where no_of_people >= 100 and ( ( prev_one_day_cnt >=100 and prev_two_day_cnt>=100 ) or ( prev_one_day_cnt >=100 and next_one_day_cnt>=100 ) or (next_one_day_cnt>=100 and next_two_day_cnt>=100) );
once again thank you for your hard work ankit i tried this below one with table1 (select a.* , lead(no_of_people ,1) over(order by visit_date ) as col1, lead(no_of_people ,2) over(order by visit_date ) as col2, lead(no_of_people ,3) over(order by visit_date ) as col3 from stadium a) ,table2(select b.* ,case when no_of_people
Hello Ankit Sir Love your content Here is an easy approach from my side . with cte as ( select *, row_number()over(order by id) as rn , id-rn as ppl_grp from stadium where no_of_people >= 100 ) select id from ( select *, count(1)over(partition by ppl_grp) as final_cnt from cte)a where a.final_cnt >=3
Good use case !! Here is my approach. select * from ( Select id,visit_date,no_of_people, case when no_of_people>100 and ( min(no_of_people) over(order by id rows between current row and 2 following)>100 or min(no_of_people) over(order by id rows between 1 preceding and 1 following)>100 or min(no_of_people) over(order by id rows between 2 preceding and current row)>100 ) then 1 end rn from stadium ) where rn=1 order by id;
Great solution bhaiya.! I came up with this , used lead() and lag() . with cte as( select id,visit_date,no_of_people, lag(no_of_people) over() as previous_day, lead(no_of_people) over () as next_day from stadium ), cte2 as (select id from cte where no_of_people>100 and previous_day>100 and next_day>100) select id,visit_date,no_of_people from stadium where id in( select id from cte2 union select id-1 from cte2 union select id+1 from cte2 ); And as always learning a lot from your videos.. Thank you again for the efforts you put in.
Hi Ankit, Thanks for making such wonderful videos. It is helping me a lot in understanding SQL much better. I have solved this problem by using your video where you have explain how to write advance SQL queries as follows: with cte as( select *, sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as Prev_2, sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as Prev_next_1, sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2 from stadium) select id, visit_date, no_of_people from cte where Prev_2 >=3 or Prev_next_1 >=3 or next_2 >= 3
;with cte as( select *,dateadd(dd,-ROW_NUMBER()over(order by visit_date),visit_date) as rwdate from stadium where no_of_people>=100 ) ,ctegrp as (select *,count(rwdate)over(partition by rwdate) as cnt from cte) select id,visit_date,no_of_people from ctegrp where cnt>=3 Thanks a lot Ankit for the Great videos. Keep it up the good work.
Thanks for the great content Ankit. I have used the date_add() to get the groups in my approach with A as ( select *, row_number() over (order by visit_date) as rno from stadium where no_of_people>100) ,B as (select *,DATE_ADD(visit_date,INTERVAL -rno DAY) as comm from A) select id,visit_date,no_of_people from ( select id, visit_date,no_of_people, count(comm) over (partition by comm) as cnt from B ) C where cnt>=3
Hi Ankit , thanks for the problem : My Solution : WITH CTE as ( select *,Case when no_of_people>100 then 1 else 0 END as flag from stadium ) ,CTE1 as ( select * ,sum(flag) over(order by id asc ROWS between 2 preceding and current row) as preceeding ,sum(flag) over(order by id asc ROWS between current row and 2 following) as following from CTE ) select id,visit_date,no_of_people from CTE1 where preceeding>=3 or following>=3
Used a similar solution as yours based on the trick that I learnt from one of the earlier videos I saw. Im a beginner and I have learnt a lot in just 1 month thanks to your SQL videos. I have one broad question: I normally get the solution easily but end up writing too many ctes (please see the code attached below). Can you please give some pointers to reduce number of ctes? One way is the common use of the having clause (I have noticed it reduces the size by atleast 1 cte for obvious reasons) and sometimes a case can also be used for the same. But generally speaking can you make a video showing tips for efficient code writing that reduces number of ctes? That will really help thank you! with cte1 as (select *, case when no_of_people>=100 then 1 else 0 end as eligible from interview_test_32), cte2 as (select *, row_number() over(order by visit_date) as rn from cte1 where eligible=1), cte3 as (select id,(id-rn) as group_no, visit_date,no_of_people from cte2), cte4 as (select *, count(1) over(partition by group_no) as count from cte3) select id, visit_date,no_of_people from cte4 where count>=3;
as usual great content. I am impressed. It is hard for me to comprehend how you lay out the problem. I guess it will take time for me to watch all your videos. Thanks a lot
select id,visit_date, no_of_people from (select *, id - rn as diff from (select *, row_number() over ()rn from (select * from stadium where no_of_people > 100)a)b)d where diff in (select diff from (select *, id - rn as diff from (select *, row_number() over ()rn from (select * from stadium where no_of_people > 100)a)b)g group by diff having count(diff) > 3) Thanks, Ankit !
Ankit used the sum by prev_2, prev_next_1, next_2 learnt from your earlier video,same here below: select * from (select *, sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as prev_2, sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as prev_next_1, sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2 from stadium) A where prev_2=3 or prev_next_1=3 or next_2=3
The second query is likely to be more performant in many modern relational database systems due to the use of window functions. Window functions are optimized by the database engine to efficiently perform calculations over a specified window of rows. This can result in better performance compared to using ROW_NUMBER() and other constructs. real-time use cases, the second query appears to be more readable and concise, which can be advantageous for maintainability and understanding the logic. This is chatGPT comments, that here second query means your solution.
Hi Ankit, does this code satisfy all the scenarios. with cte as( select *,case when (lead(no_of_people,1,no_of_people) over(order by visit_date))>=100 and no_of_people>=100 and (lead(no_of_people,2,no_of_people) over(order by visit_date))>=100 then 1 else 0 end as next_entry from stadium ) select * from cte where next_entry=1
Please do review my approach with CTE as (Select *, count(id-rn) over(partition by (id-rn)) as dif from (Select *, row_number() over(order by visit_date) as rn from stadium where no_of_people>=100) as F) Select id,visit_date,no_of_people from CTE where dif>3
Thanks for the question Ankit. Below is my solution using lead,lag with cte_use as (select id,visit_date,no_of_people, case when no_of_people>=100 and isnull(lead(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lead(no_of_people,2) over(order by visit_date),0)>=100 then 1 else 0 end as next_two, case when no_of_people>=100 and isnull(lag(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lag(no_of_people,2) over(order by visit_date),0)>=100 then 1 else 0 end as previous_two, case when no_of_people>=100 and isnull(lag(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lead(no_of_people,1) over(order by visit_date),0)>=100 then 1 else 0 end as current_previous_next from stadium) select * from cte_use where next_two=1 or previous_two=1 or current_previous_next=1
Hello Ankit, I have come with my own solution. with cte as( select *, CASE WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lead(no_of_people,2) over(order by visit_date)>100 then 1 WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lag(no_of_people) over(order by visit_date)>100 then 1 WHEN no_of_people>100 and lag(no_of_people) over(order by visit_date)>100 and lag(no_of_people,2) over(order by visit_date)>100 then 1 ELSE 0 END as "status" from stadium) select id,visit_date,no_of_people from cte where status=1; Thanks for the video.
with cte as (select *, (id-row_no) as diff from ( select *, row_number() over(order by visit_date) as row_no from stadium where no_of_people >=100) a), cte_2 as (select diff, count(diff) from cte group by diff having count(diff)>3) select id, visit_date, no_of_people from cte where diff in (select diff from cte_2)
Hi Ankit...Thanks for the content...My approach WITH CTE AS( SELECT *,id-(DENSE_RANK()OVER(ORDER BY id)) AS rn FROM stadium WHERE no_of_people>=100), CTE_2 AS( SELECT *,COUNT(*)OVER(PARTITION BY rn ORDER BY rn) AS total_count FROM CTE) SELECT id,visit_date,total_count FROM CTE_2 WHERE total_count>=3
Thanks for the video, here is my solution. Learnt the concept from one of your previous videos. WITH CTE AS (SELECT S.*, SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as prev_day_count, SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as curr_day_count, SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as next_day_count FROM Stadium S) # SELECT * FROM CTE; SELECT id, visit_date, people FROM CTE WHERE curr_day_count >= 3 OR prev_day_count >= 3 OR next_day_count >= 3 ORDER BY visit_date ASC;
easiest one select id,visit_date,no_of_people from ( select *, case when min(no_of_people) over(order by id range between current row and 2 following) > 100 then 1 else 0 end as flag from stadium)s where flag=1
@@ankitbansal6 have been following you on linked In as well.. you and some other folks are doin a commendable job by sharing your knowledge … please continue to do so😍
with cte as ( select *,row_number() over(order by id) as idd from ( select *, lag(visit_date,1) over(order by visit_date) as nxt_date from stadium )a where no_of_people>100 and visit_date>nxt_date ) Select id,visit_date,no_of_people from cte where id-idd>1 Howz the approach brother ??
All you need is a lag function then minus current date from previous date and just take which is= to 1 day on the cte with first filter where no_of_people >=100
Your taught this approach in one of the previous video thatswhy after saw this question this logic immediately strike into my mind with cte as( select id,visit_date,no_of_people as curr_row,lag(no_of_people) over(order by visit_date asc) as prev_row, lag(no_of_people,2) over(order by visit_date) as prev_two_row, lead(no_of_people) over(order by visit_date) as next_row, lead(no_of_people,2) over(order by visit_date) as next_two_row from stadium) select visit_date,curr_row as no_of_people from cte where (curr_row>=100 and next_row>=100 and next_two_row>=100) or (curr_row>=100 and prev_row>=100 and next_row>=100) or (curr_row>=100 and prev_row>=100 and prev_two_row>=100)
Great video as always.. here's my take on the solution: ========================================================= with cte as (select *, case when no_of_people =100 then 1 else 0 end) over (order by id)) end as indicator from stadium) select id, visit_date, no_of_people from (Select *, count(indicator) over(partition by indicator) cnt from cte) sq1 where cnt>=3 and indicator0 ==========================================================
With cte as ( Select stadium *, case When people>100 and ( lead(people,1) over( order by I'd)>100 and lead(people,2) over(order by I'd) >100 Or lag(people,1) over( order by I'd)>100 and Lag(people,2) over(order by I'd)>100 ) Then 1 else 0 end as newcol ) Select I'd, visitdate,people from cte where newcol=1;
select A.id, A.visit_date, A.no_of_people from( select *, sum(no_of_people) over(order by visit_date rows between 2 preceding and current row) as moving_3_days_sum from stadium) as A where A.moving_3_days_sum/3>100 and A.no_of_people>100;
My approach : with cte as ( select * ,(case when no_of_people>=100 then 1 else 0 end) as letssee from stadium), cte2 as ( select *, (case when (lead(letssee) over(order by visit_date)=1 and lead(letssee,2) over(order by visit_date)=1 and letssee =1) or (lead(letssee) over(order by visit_date)=1 and letssee=1 and lag(letssee) over(order by visit_date)=1) or (letssee=1 and lag(letssee) over(order by visit_date)=1 and lag(letssee,2) over(order by visit_date)=1) then 1 else 0 end) as tryy from cte) select * from cte2 where tryy=1
Hi Ankt,this is also learnt from your other video: with cte2 as ( with cte as( select *,(case when no_of_people>100 then 1 else 0 end) as flag from stadium) select *,sum(flag) over(rows between current row and 2 following) as next3 from cte) select distinct s.id,s.visit_date,s.no_of_people from cte2 c2 join stadium s on c2.next3=3 and (s.id=c2.id or s.id=c2.id+1 or s.id=c2.id+2 );
with cte as (select *,lead(no_of_people,1,no_of_people) over(order by visit_date) as lead1,lead(no_of_people,2,no_of_people) over(order by visit_date) as lead2 from stadium) select * from cte where no_of_people>100 and lead1>100 and lead2>100 How about this?
Thanks sir for this great solution.... My Solution using LEAD and LAG Window Function select id, visit_date,no_of_people from(select *, case when no_of_people >= 100 and lead(no_of_people) over()>100 and lead(no_of_people,2) over()>100 and lead(no_of_people,3) over()>100 then 1 when no_of_people >= 100 and lag(no_of_people,1) over()>100 and lead(no_of_people,1) over()>100 and lead(no_of_people,2) over()>100 then 1 when no_of_people >= 100 and lag(no_of_people,2) over()>100 and lag(no_of_people,1) over()>100 and lead(no_of_people,1) over()>100 then 1 when no_of_people >= 100 and lag(no_of_people,1) over()>100 and lag(no_of_people,2) over()>100 and lag(no_of_people,3) over()>100 then 1 end as noofpeople from stadium) x where x.noofpeople0;
Similar Approach: with cte AS( select *, count(1) over(partition by diff) as tot_cnt from ( select *, id - ROW_NUMBER() over(order by id) as diff from stadium where no_of_people > 100 )A ) select * from stadium where id in ( select id from cte where tot_cnt >= 3 )
Hi, Could you please cover other skill of Data engineer as well like Data modelling, DM , and core skills which need for a Data Engineer profile to clear interviews for experienced people?
My approach WITH CTE AS ( SELECT * , LEAD(NO_OF_PEOPLE,1,101) OVER(ORDER BY VISIT_DATE) AS R1, LEAD(NO_OF_PEOPLE,2,101) OVER(ORDER BY VISIT_DATE) AS R2 FROM STADIUM ) SELECT ID,VISIT_DATE,NO_OF_PEOPLE FROM CTE WHERE NO_OF_PEOPLE >100 AND R1>100 AND R2>100
with cte as ( Select *, row_number() over(order by id) as rn from stadium where no_of_people>100 ) Select id,(case when id-rn=2 then visit_date end) as visit, no_of_people from cte where (case when id-rn=2 then visit_date end) is not null
with cte as (select * , lead(people) over(order by visit_date) as ld, lag(people) over(order by visit_date) as lg, lead(people, 2) over(order by visit_date) as ld1, lag(people, 2) over(order by visit_date) as lg1 from stadium) select id, visit_date, people from cte where (people >= 100 and ld >= 100 and ld1 >= 100) or (people >= 100 and lg >= 100 and ld >= 100) or (people >= 100 and lg >= 100 and lg1 >= 100) not sure if this is faster or slower but definitely easier to write
Short and better solution, I used UNION when I did this query on Leetcode. select id,visit_date,no_of_people from ( select a.id,a.visit_date,a.no_of_people from stadiums a inner join stadiums b on a.id=b.id+1 and a.no_of_people>=100 and b.no_of_people>=100 inner join stadiums c on a.id=c.id+2 and a.no_of_people>=100 and c.no_of_people>=100 union select a.id,a.visit_date,a.no_of_people from stadiums a inner join stadiums b on a.id=b.id-1 and a.no_of_people>=100 and b.no_of_people>=100 inner join stadiums c on a.id=c.id-2 and a.no_of_people>=100 and c.no_of_people>=100 union select a.id,a.visit_date,a.no_of_people from stadiums a inner join stadiums b on a.id=b.id-1 and a.no_of_people>=100 and b.no_of_people>=100 inner join stadiums c on a.id=c.id+1 and a.no_of_people>=100 and c.no_of_people>=100 ) A order by id;
with cte as (select *, ifnull(lead(no_of_people,1) over(order by visit_date),101) as lg1, ifnull(lead(no_of_people,2) over(order by visit_date),101) as lg2 from stadium) select id, visit_date,no_of_people from cte where no_of_people>100 and lg1>100 and lg2>100
with cte as (SELECT *, isnull(min(no_of_people) over (order by visit_date rows between 1 following and 2 following), no_of_people) as ppl_cons from stadium) SELECT * from cte where no_of_people >=100 and ppl_cons>=100
;with cte as ( select *, case when no_of_people>100 and LEAD(no_of_people)over(order by visit_date) >100 and LEAD(no_of_people,2)over(order by visit_date) >100 then 1 when no_of_people>100 and Lag(no_of_people)over(order by visit_date) >100 and Lag(no_of_people,2)over(order by visit_date) >100 then 1 else 0 end as flag from stadium ) select * from cte where flag=1
Hi , ankit this is my solution with cte1 as(select* ,ntile(2)over(order by (select null)) as grp from stadium) ,cte2 as(select * from cte1 where no_of_people>100) select * from cte2 where grp in(select grp from cte2 group by grp having count(grp)>=3)
with ranges as ( select min(visit_date) start,max(visit_date) end from( select * , id-row_number() over(partition by diff order by visit_date) as grp from ( select * , case when people >100 then 1 else 0 end as diff from stadium ) a) b where people >100 group by grp having datediff(end,start) >=3 ) select s.* from stadium s join ranges r where s.visit_date between r.start and r.end
can we use below query .................................... with cte as ( select *,Lead(no_of_people,1) over(order by visit_date) ln1 ,Lead(no_of_people,2) over(order by visit_date)ln2 ,Lead(no_of_people,3) over(order by visit_date)ln3 from stadium ) select * from cte where ln1>100 and ln2>100 and ln3>100 ;
hey ankit, pls make me understand the data, is it assumed that data is only having cases with consecutive ids and greater than 100? because there could be another possibilty where consecutive ids condition is not met but greater than 100 is satisfied
Hi Ankit, thanks for posting. Here is my solution: WITH consecutive_increments AS ( SELECT id, visit_date, no_of_people, LEAD(visit_date, 1, 0) OVER (ORDER BY visit_date) AS second_date, LEAD(no_of_people, 1, 0) OVER (ORDER BY visit_date) AS second_no_of_people, LEAD(visit_date, 2, 0) OVER (ORDER BY visit_date) AS third_date, LEAD(no_of_people, 2, 0) OVER (ORDER BY visit_date) AS third_no_of_people FROM stadium ), consecutive_increments_2 AS ( SELECT visit_date, second_date, third_date, no_of_people, second_no_of_people, third_no_of_people FROM consecutive_increments WHERE no_of_people >= 100 AND second_no_of_people >= 100 AND third_no_of_people >= 100 ) SELECT visit_date, no_of_people FROM consecutive_increments_2 UNION SELECT second_date AS visit_date, second_no_of_people FROM consecutive_increments_2 UNION SELECT third_date AS visit_date, third_no_of_people FROM consecutive_increments_2 ORDER BY visit_date;
with cte1 as( select *,row_number() over(order by visit_date) as rn, id-row_number() over(order by visit_date) as grp from stadium where no_of_people>=100 ),cte2 as( select count(id) as cnt,grp from cte1 group by grp having count(1)>=3 ) select * from cte1 where grp in(select grp from cte2)
MYSQL Solution with base as (select *,day(date_sub(visit_date,interval row_number() over() day)) as diff from stadium where no_of_people >=100), base_cnt as ( select *,count(diff) over(partition by diff) as cnt from base) select id,visit_date,no_of_people from base_cnt where cnt >=3
Select * from stadium for consective question -> different approaches a. lead1, lead2 b. lead1, lead2, lag1,lag2 c. sum(case) 2 preceding and current row as Prev_2, , 1 preceding and 1 following) as Prev_next_1 , current row and 2 following) as next_2 d. ntile - groupuing d. dense_rank -> ,id-(DENSE_RANK()OVER(ORDER BY id)) e. ankit -> row_number & id-rn ;with cte as ( Select *, ROW_NUMBER() over(order by visit_date) as rn, id-ROW_NUMBER() over(order by visit_date) as grp from stadium where no_of_people>=100 ) select * from cte where grp in (select grp from cte group by grp having count(*)>=3 )
my solution: with cte as ( select * ,isnull(lead(no_of_people,1) over (order by id),LAG(no_of_people,1) over(order by id)) as p1 ,isnull(lead(no_of_people,2) over (order by id),LAG(no_of_people,2) over(order by id)) as p2 from stadium ), cte2 as ( select * ,case when no_of_people>=100 and p1>=100 and p2>=100 then 1 else 0 end as mrk from cte ) select id,visit_date,no_of_people from cte2 where mrk=1
Is this approach to the problem correct? Please find my solution below : Select id,visit_date,no_of_people from (Select *, SUM(temp.flag) Over(order by id) as running_sum from (Select * , CASE WHEN no_of_people > 100 THEN 1 ELSE 0 END as flag from stadium) temp) final_table where final_table.running_sum >= 3; Please help😅
WITH cte AS ( SELECT id FROM Stadium WHERE people > 99 ) SELECT * FROM Stadium WHERE people > 99 AND ((id-1 IN (TABLE cte) AND id-2 IN (TABLE cte)) OR (id+1 IN (TABLE cte) AND id+2 IN (TABLE cte)) OR (id+1 IN (TABLE cte) AND id-1 IN (TABLE cte)))
with cte as (select id,visit_date,no_of_people,ROW_NUMBER() over (order by (select 1)) r, id-ROW_NUMBER() over (order by (select 1)) x from stadium where no_of_people>=100), cte2 as ( select *,count(1) over (partition by x order by (select 1)) y from cte) select id,visit_date,no_of_people from cte2 where y>=3
with cte as(Select *, lead(no_of_people, 1) over (order by visit_date) as lead_1, lead(no_of_people, 2) over (order by visit_date) as lead_2, lag(no_of_people, 1) over (order by visit_date) as lag_1, lag(no_of_people, 2) over (order by visit_date) as lag_2 from stadium) Select id, visit_date, no_of_people from (Select *, case when no_of_people> 99 and lead_1> 99 AND lead_2>99 then 'Y' when no_of_people> 99 and lag_1> 99 AND lag_2>99 then 'Y' End as Status from cte) where Status =='Y'
WITH CTE AS( select s.*, dense_rank()over(order by visit_date) as rank, id-dense_rank()over(order by visit_date)as grp from stadium s where no_of_people>=100 ) SELECT ID,VISIT_DATE,NO_OF_PEOPLE FROM CTE WHERE GRP>=2 ; IS IT FINE?
select id,visit_date,no_of_people from( select * ,count(*) over(partition by rd) as ct from (select * , (id - row_number( )over(order by id )) as rd from stadium where no_of_people >= 100)) where ct>= 3;
-- with frame clause in windows function with cte as(select *, case when no_of_people>=100 then 1 else 0 end, sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as previous_2d_sum, sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as 1_up_down, sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as current_2_down from stadium) select id,visit_date,no_of_people from cte where previous_2d_sum=3 or 1_up_down=3 or current_2_down=3;
select * from ( select * ,lag(no_of_people,1) over (order by visit_date ) as prev_1 ,lag(no_of_people,2,0) over (order by visit_date ) as prev_2 ,lead(no_of_people,1,0) over (order by visit_date ) as next_1 ,lead(no_of_people,2,0) over (order by visit_date ) as next_2 from stadium)A where (no_of_people>100 and prev_1>100 and prev_2>100) or (no_of_people>100 and next_1>100 and next_2>100) or (no_of_people>100 and prev_1>100 and next_1>100)
I tried differently; with cte as( select *, (case when (no_of_people >= 100 and lead(no_of_people, 1, null) over(order by id) >=100 and lead(no_of_people, 2, null) over(order by id) >= 100) then 1 else 0 end) ld, (case when (no_of_people >= 100 and lag(no_of_people, 1, null) over(order by id) >=100 and lag(no_of_people, 2, null) over(order by id) >= 100) then 1 else 0 end) lg from stadium ) select * from cte where ld = 1 or lg =1;
with prev_count as (select *,lag(no_of_people,1,0) over(order by id) as prev_1 ,lag(no_of_people,2,0) over(order by id) as prev_2, lead(no_of_people,1,0) over(order by id) as next_1, lead(no_of_people,2,0) over(order by id) as next_2 from stadium),final as (select *, case when no_of_people>=100 and prev_1 >= 100 and prev_2>= 100 then "Y" when no_of_people>=100 and prev_1 >= 100 and next_1 >= 100 then "Y" when no_of_people>=100 and next_1 >= 100 and next_2 >= 100 then "Y" else "N" end as 'status' from prev_count) select id,visit_date,no_of_people from final where status = 'Y'; or with cte as (select * from stadium where no_of_people >= 100), cte2 as (select *, id-row_number() over (order by id) as rnk from cte) select id,visit_date,no_of_people from cte2 where rnk in (select rnk from cte2 group by 1 having count(rnk) >= 3)
with stadium_cte as( select * ,ROW_NUMBER() over(order by visit_date) as rno from stadium where no_of_people>=100), count_cte as( select id, visit_date, no_of_people, id-rno as diff from stadium_cte), final_cte as( select diff,count(diff) as count from count_cte group by diff having count(diff)>=3) select id,visit_date,no_of_people from count_cte c1 inner join final_cte c2 on c1.diff=c2.diff
select * from stadium where id in ( with cte as( select id, lag(id,1) over (order by id) as id1,lag(id,2) over (order by id) as id2, visit_date,no_of_people, lag(no_of_people,1) over (order by id) as p1, lag(no_of_people,2) over (order by id) as p2 from stadium order by visit_date asc) select id from cte where no_of_people > 100 and p1 >100 and p2 >100 union select id1 from cte where no_of_people > 100 and p1 >100 and p2 >100 union select id2 from cte where no_of_people > 100 and p1 >100 and p2 >100)
with forward_consecutive as ( select * , lead(no_of_people,1) over(order by visit_date ) as day2, lead(no_of_people,2) over(order by visit_date) as day3 from stadium ), backward_consecutive as ( select * , lag(no_of_people,1) over(order by visit_date ) as day2, lag(no_of_people,2) over(order by visit_date) as day3 from stadium ) select * from (select * from backward_consecutive union all select * from forward_consecutive) aa where no_of_people >100 and day2>100 and day3>100 ;
Hi Ankit please check and verity ;with cte as( select *, case when no_of_people>=100 then 1 else 0 end as flag from stadium) ,cte2 as( select *,ROW_NUMBER() over (partition by flag order by visit_date) rn ,dateadd(day,-1*ROW_NUMBER() over (partition by flag order by visit_date) ,visit_date) grp from cte) select id,visit_date,no_of_people from cte2 where flag=1 and grp in (select grp from cte2 group by flag,grp having count(9)>=3 and flag=1)
without using id column : with cte as (select *, lag(visit_date) over( order by visit_date) as first_date, lag(visit_date,2) over( order by visit_date) as second_date, lag(visit_date,3) over( order by visit_date) as third_date from stadium), cte2 as ( select case when visit_date-first_date=1 and visit_date-second_date=2 and visit_date-third_date=3 then id end as win from cte) select a.id,a.visit_date,a.no_of_people from cte a join cte2 b on a.id=b.win where a.no_of_people>100
By doing this code, it also gives us the same result. Can you please check here: ( select *, row_number() over(order by id) as rnk, id-row_number() over(order by id) as grp from stadium where no_of_people >=100 ) select * from grp_num where grp>=2
with CTE as (select *, min(no_of_people) over(order by visit_date rows between 1 following and 1 following) as nextday, min(no_of_people) over(order by visit_date rows between 2 following and 2 following) as nexttonextday from stadium ) select id, visit_date,no_of_people from (select *, case when no_of_people >100 and nextday>100 and nexttonextday>100 then 'TRUE' when no_of_people >100 and nextday>100 and nexttonextday is NULL then 'TRUE' when no_of_people >100 and nextday is NULL and nexttonextday is NULL then 'TRUE' else 'FALSE' end as output from CTE )A where output='TRUE'
--Leetcode Hard SQL Problem | Human Traffic of Stadium with cte as (select id, sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between 2 preceding and current row) two_pred, sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between 1 preceding and 1 following) two_pred_next, sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between current row and 2 following) two_next from stadium ) select id from cte where two_pred = 3 or two_pred_next = 3 or two_next = 3
with cts as (select *, row_number() over(order by id) as rownum, id-ROW_NUMBER() over(order by id) as diff from dbo.stadium where no_of_people >= 100) I am trying to use the above query and I am getting teh below error. Msg 102, Level 15, State 1, Line 24 Incorrect syntax near ';'. Can someone help me to resolve this?
with cte1 as (select *,lag(indicator,1,indicator) over(order by visit_date) i2 from (select *,case when no_of_people>=100 then "y" else "n" end indicator from stadium) a), cte2 as (select *,sum(case when indicator="n" and i2="y" then 1 else 0 end) over(order by visit_date) grp_key from cte1), cte3 as (select min(visit_date)k_d,max(visit_date) m_d,grp_key from cte2 group by grp_key having count(*)>=4) select visit_date,no_of_people from cte3 join cte2 using (grp_key) where visit_date between k_d and m_d having no_of_people>=100 (my hard answer !)
select id,visit_date,people from(select *, case when people>=100 and ((lag(people) over(order by id)>=100 and lag(people,2) over(order by id)>=100) or (lead(people) over(order by id)>=100 and lead(people,2) over(order by id)>=100) or (lag(people) over(order by id)>=100 and lead(people) over(order by id)>=100)) then 1 else 0 end as flag from stadium) res where flag=1
with cte as (select id, visit_date , no_of_people , lead(no_of_people,1,no_of_people) over (order by visit_date) as ln , lead(no_of_people,2,no_of_people) over (order by visit_date) as ln2 from stadium) select id, visit_date , no_of_people from cte where no_of_people >= 100 and ln >= 100 and ln2 >= 100
with people_cte as( select * from stadium where no_of_people >=100), people_cte_two as ( select *, isnull(lead(id,1) over(order by id),0) as second_num ,isnull(lead(id,2) over(order by id),0) as third_num from people_cte), third_cte as( select * ,second_num-id as first_diff ,third_num-second_num as second_diff from people_cte_two) select id,visit_date,no_of_people from third_cte where first_diff=1 and second_diff=1 or second_diff=0 or second_diff
My Solution: -- select * from Stadium; with CTE AS ( SELECT *, lead(people,1) over(order by visit_date) AS lead_1st_day, lead(people,2) over(order by visit_date) AS lead_2nd_day, lag(people,1) over(order by visit_date) AS lag_1st_day, lag(people,2) over(order by visit_date) AS lag_2nd_day FROM STADIUM ) -- Select * from CTE; SELECT id, visit_date, people FROM CTE WHERE (people >= 100 AND lead_1st_day >= 100 AND lead_2nd_day >= 100) OR (people >= 100 AND lead_1st_day >= 100 AND lag_1st_day >= 100) OR (people >= 100 AND lag_1st_day >= 100 AND lag_2nd_day >= 100) ORDER BY visit_date;
How about this sir: with t as(select id, visit_date,no_of_people,lag(no_of_people, 1) over (order by visit_date)lag1,lag(no_of_people, 2) over (order by visit_date)lag2 ,lead(no_of_people, 1) over (order by visit_date)lead1,lead(no_of_people, 2) over (order by visit_date)lead2 from stadium ) select id,visit_Date,no_of_people from t where no_of_people >= 100 and (lead1 >= 100 and lead2 >= 100 or (lag1 >= 100 and lag2 >=100))
with cte AS ( select * from stadium where no_of_people >= 100 ),ctf AS ( select *,lead(id,1,id) over(order by id) as nxt_id, (lead(id,1,id) over(order by id) - id) as diff from cte ),ctk AS ( select *, row_number() over(order by id) as rn, (id - row_number() over(order by id)) as diff_new from ctf where diff in (1,0) ) select id,visit_date,no_of_people from ctk JOIN ( select diff_new from ctk group by diff_new having count(*) >= 3 )A on ctk.diff_new = a.diff_new
WITH cte AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY id ASC) AS rnk1 FROM stadium ORDER BY id ) , cte2 AS ( SELECT *, ROW_NUMBER() OVER(order by id) AS rnk2, rnk1 - ROW_NUMBER() OVER(order by id) as key FROM cte WHERE no_of_people>=100 ) , cte3 AS ( SELECT *, COUNT(1) OVER(PARTITION BY key) FROM cte2 ) SELECT id,visit_date,no_of_people FROM cte3 WHERE count>=3
@@ankitbansal6 Thank you, a suggestion would be to include the leetcode number in the title as many search based on that and thx for the solid content!!
WITH cte AS (SELECT *,rank() OVER(ORDER BY visit_date) AS rnk,id-rank() OVER(ORDER BY visit_date) AS grp FROM stadium WHERE no_of_people>100) SELECT * FROM cte WHERE grp IN (SELECT grp FROM cte GROUP BY grp HAVING count(1)>3 )
select id,visit_Date,no_of_people from (select id,visit_Date,no_of_people ,count(*) over(partition by cnt ) cnt1 from (select id,visit_Date,no_of_people,id -rownum cnt from stadium where no_of_people >100) a) where cnt1 >=3 order by id;
WITH Consecutive_days AS ( SELECT id, visit_date, no_of_people, CASE WHEN no_of_people > 100 AND LAG(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LAG(no_of_people, 2) OVER (ORDER BY visit_date) > 100 THEN 1 WHEN no_of_people > 100 AND (LAG(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LEAD(no_of_people, 1) OVER (ORDER BY visit_date) > 100) THEN 1 WHEN no_of_people > 100 AND LEAD(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LEAD(no_of_people, 2) OVER (ORDER BY visit_date) > 100 THEN 1 ELSE 0 END AS is_consecutive FROM stadium ) SELECT id, visit_date, no_of_people FROM Consecutive_days WHERE is_consecutive = 1 ORDER BY visit_date;
WITH cte AS ( SELECT id, visit_date, no_of_people, COUNT(rn) OVER (PARTITION BY rn) as cnt_flag FROM ( SELECT * ,id - ROW_NUMBER() OVER (ORDER BY visit_date) AS rn FROM stadium WHERE no_of_people>=100) T) SELECT id, visit_date, no_of_people FROM cte WHERE cnt_flag>=3
with cte as ( select *, ROW_NUMBER()OVER(ORDER BY visit_date) as rw_num, id - ROW_NUMBER()OVER(ORDER BY visit_date) as grp from stadium where no_of_people >= 100), cte2 AS ( select grp,COUNT(no_of_people) as Num FROM CTE group by grp HAVING COUNT(no_of_people) >= 3) Select id,visit_date,no_of_people from cte as c inner join cte2 as c1 on c.grp = c1.grp
Thanks for the logical solution . Achived the same using lead lag function
with CTE AS (
SELECT *,
lead(no_of_people,1) over(order by visit_date) AS lead_1st_day,
lead(no_of_people,2) over(order by visit_date) AS lead_2nd_day,
lag(no_of_people,1) over(order by visit_date) AS lag_1st_day,
lag(no_of_people,2) over(order by visit_date) AS lag_2nd_day
FROM STADIUM )
SELECT id,visit_date,no_of_people FROM cte WHERE no_of_people>=100 and ((lead_1st_day>=100 AND lead_2nd_day>=100) OR
(lag_1st_day>=100 and lag_2nd_day>=100))
Thank you 😊
great way to optimise
@@ankitbansal6 hi. this wont work for days where no_of_people>=100 which only have lead_1st_day>100 and lag_1st_day>=100 but lead_2nd_day and lag_2nd_day less than 100.
correct solution-
with cte as (select *, lead(no_of_people,1,0) over (order by id) as lead1 ,
lead(no_of_people,2,0) over (order by id) as lead2,
lag(no_of_people,1,0) over (order by id) as lag1,
lag(no_of_people,2,0) over (order by id) as lag2 from stadium) ,
cte2 as (
select * , case when lag1
@@anubhavsahay8116 no need of one more of cte we can add one extra condition to Punit code...i.e
with CTE AS (
SELECT *,
lead(no_of_people,1) over(order by visit_date) AS lead_1st_day,
lead(no_of_people,2) over(order by visit_date) AS lead_2nd_day,
lag(no_of_people,1) over(order by visit_date) AS lag_1st_day,
lag(no_of_people,2) over(order by visit_date) AS lag_2nd_day
FROM STADIUM )
--select * from cte
SELECT id,visit_date,no_of_people FROM cte WHERE no_of_people>=100 and ((lead_1st_day>=100 AND lead_2nd_day>=100) OR
(lag_1st_day>=100 and lag_2nd_day>=100) or (lag_1st_day>=100 and lead_1st_day>=100))
let us know if this wont work
Super approach 🔥
Excellent Approach Ankit . Need to practise to adapt the thinking approach. I was used the legacy thinking approach :
with cte1 as
(Select *
,lag(no_of_people,1,0) over ( order by visit_date ) as prev_one_day_cnt
,lag(no_of_people,2,0) over ( order by visit_date ) as prev_two_day_cnt
,lead(no_of_people,1,0) over ( order by visit_date ) as next_one_day_cnt
,lead(no_of_people,2,0) over ( order by visit_date ) as next_two_day_cnt
from stadium)
Select visit_date,no_of_people
from cte1
where no_of_people >= 100 and
(
( prev_one_day_cnt >=100 and prev_two_day_cnt>=100 )
or
( prev_one_day_cnt >=100 and next_one_day_cnt>=100 )
or
(next_one_day_cnt>=100 and next_two_day_cnt>=100)
);
once again thank you for your hard work ankit i tried this below one
with table1 (select a.* , lead(no_of_people ,1) over(order by visit_date ) as col1,
lead(no_of_people ,2) over(order by visit_date ) as col2,
lead(no_of_people ,3) over(order by visit_date ) as col3
from stadium a)
,table2(select b.* ,case when no_of_people
Hello Ankit Sir Love your content
Here is an easy approach from my side .
with cte as (
select *,
row_number()over(order by id) as rn ,
id-rn as ppl_grp
from stadium
where no_of_people >= 100
)
select id from (
select *,
count(1)over(partition by ppl_grp) as final_cnt
from cte)a
where a.final_cnt >=3
Good use case !!
Here is my approach.
select * from (
Select id,visit_date,no_of_people,
case when no_of_people>100
and (
min(no_of_people) over(order by id rows between current row and 2 following)>100
or
min(no_of_people) over(order by id rows between 1 preceding and 1 following)>100
or
min(no_of_people) over(order by id rows between 2 preceding and current row)>100
)
then 1 end rn
from stadium ) where rn=1
order by id;
This one is most simple and straightforward solution. Thanks.
Great solution bhaiya.!
I came up with this , used lead() and lag() .
with
cte as(
select id,visit_date,no_of_people,
lag(no_of_people) over() as previous_day,
lead(no_of_people) over () as next_day
from stadium
),
cte2 as
(select id from cte
where no_of_people>100 and previous_day>100 and next_day>100)
select
id,visit_date,no_of_people from stadium
where id in(
select id from cte2
union
select id-1 from cte2
union
select id+1 from cte2
);
And as always learning a lot from your videos..
Thank you again for the efforts you put in.
Looks good 👍
Hi Ankit, Thanks for making such wonderful videos. It is helping me a lot in understanding SQL much better. I have solved this problem by using your video where you have explain how to write advance SQL queries as follows:
with cte as(
select *,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as Prev_2,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as Prev_next_1,
sum(case when no_of_people >= 100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2
from stadium)
select id, visit_date, no_of_people
from cte
where Prev_2 >=3
or Prev_next_1 >=3
or next_2 >= 3
Looks great 👍
;with cte as(
select *,dateadd(dd,-ROW_NUMBER()over(order by visit_date),visit_date) as rwdate from stadium where no_of_people>=100
)
,ctegrp as
(select *,count(rwdate)over(partition by rwdate) as cnt from cte)
select id,visit_date,no_of_people from ctegrp where cnt>=3
Thanks a lot Ankit for the Great videos. Keep it up the good work.
Thanks for the great content Ankit. I have used the date_add() to get the groups in my approach
with A as (
select *,
row_number() over (order by visit_date) as rno
from stadium
where no_of_people>100)
,B as
(select *,DATE_ADD(visit_date,INTERVAL -rno DAY) as comm
from A)
select id,visit_date,no_of_people from
(
select id,
visit_date,no_of_people,
count(comm) over (partition by comm) as cnt
from B
) C where cnt>=3
What is interval here ?
The row number calculated in cte A acts as the interval in the dateadd function
I don't think this is SQL server. Which database you are using?
I am using MySQL
Hi Ankit , thanks for the problem :
My Solution :
WITH CTE as (
select *,Case when no_of_people>100 then 1 else 0 END as flag from stadium
)
,CTE1 as (
select *
,sum(flag) over(order by id asc ROWS between 2 preceding and current row) as preceeding
,sum(flag) over(order by id asc ROWS between current row and 2 following) as following
from CTE
)
select id,visit_date,no_of_people from CTE1 where preceeding>=3 or following>=3
You missed 1 preceding and 1 following
Used a similar solution as yours based on the trick that I learnt from one of the earlier videos I saw. Im a beginner and I have learnt a lot in just 1 month thanks to your SQL videos. I have one broad question: I normally get the solution easily but end up writing too many ctes (please see the code attached below). Can you please give some pointers to reduce number of ctes? One way is the common use of the having clause (I have noticed it reduces the size by atleast 1 cte for obvious reasons) and sometimes a case can also be used for the same. But generally speaking can you make a video showing tips for efficient code writing that reduces number of ctes? That will really help thank you!
with cte1 as
(select *,
case when no_of_people>=100 then 1 else 0 end as eligible
from interview_test_32),
cte2 as
(select *, row_number() over(order by visit_date)
as rn from cte1 where eligible=1),
cte3 as
(select id,(id-rn) as group_no, visit_date,no_of_people from cte2),
cte4 as
(select *, count(1) over(partition by group_no) as count from cte3)
select id, visit_date,no_of_people from cte4 where count>=3;
as usual great content. I am impressed. It is hard for me to comprehend how you lay out the problem. I guess it will take time for me to watch all your videos. Thanks a lot
Thanks for watching!
select id,visit_date, no_of_people from
(select *, id - rn as diff from
(select *, row_number() over ()rn from
(select * from stadium where no_of_people > 100)a)b)d
where diff in
(select diff from
(select *, id - rn as diff from
(select *, row_number() over ()rn from
(select * from stadium where no_of_people > 100)a)b)g
group by diff
having count(diff) > 3)
Thanks, Ankit !
Ankit used the sum by prev_2, prev_next_1, next_2 learnt from your earlier video,same here below:
select * from
(select *,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as prev_2,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as prev_next_1,
sum(case when no_of_people>100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as next_2
from stadium) A
where prev_2=3 or prev_next_1=3 or next_2=3
The second query is likely to be more performant in many modern relational database systems due to the use of window functions. Window functions are optimized by the database engine to efficiently perform calculations over a specified window of rows. This can result in better performance compared to using ROW_NUMBER() and other constructs.
real-time use cases, the second query appears to be more readable and concise, which can be advantageous for maintainability and understanding the logic.
This is chatGPT comments, that here second query means your solution.
Hi Ankit,
does this code satisfy all the scenarios.
with cte as(
select *,case when (lead(no_of_people,1,no_of_people) over(order by visit_date))>=100 and no_of_people>=100 and (lead(no_of_people,2,no_of_people) over(order by visit_date))>=100 then 1 else 0 end as next_entry from stadium
)
select * from cte where next_entry=1
Please do review my approach
with CTE as
(Select *,
count(id-rn) over(partition by (id-rn)) as dif
from
(Select *,
row_number() over(order by visit_date) as rn
from stadium
where no_of_people>=100) as F)
Select id,visit_date,no_of_people from
CTE where dif>3
Thanks for the question Ankit. Below is my solution using lead,lag
with cte_use as
(select id,visit_date,no_of_people, case when no_of_people>=100 and isnull(lead(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lead(no_of_people,2) over(order by visit_date),0)>=100 then 1 else 0 end as next_two,
case when no_of_people>=100 and isnull(lag(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lag(no_of_people,2) over(order by visit_date),0)>=100 then 1 else 0 end as previous_two,
case when no_of_people>=100 and isnull(lag(no_of_people,1) over(order by visit_date),0)>=100 and isnull(lead(no_of_people,1) over(order by visit_date),0)>=100 then 1 else 0 end as current_previous_next
from stadium)
select * from cte_use
where next_two=1 or previous_two=1 or current_previous_next=1
Hello Ankit,
I have come with my own solution.
with cte as(
select *,
CASE
WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lead(no_of_people,2) over(order by visit_date)>100 then 1
WHEN no_of_people>100 and lead(no_of_people) over(order by visit_date)>100 and lag(no_of_people) over(order by visit_date)>100 then 1
WHEN no_of_people>100 and lag(no_of_people) over(order by visit_date)>100 and lag(no_of_people,2) over(order by visit_date)>100 then 1
ELSE 0
END as "status"
from stadium)
select id,visit_date,no_of_people from cte where status=1;
Thanks for the video.
Great video on Gaps & Islands problem, Thank you Ankit!
with cte as
(select *, (id-row_no) as diff
from (
select *, row_number() over(order by visit_date) as row_no
from stadium
where no_of_people >=100) a),
cte_2 as
(select diff, count(diff)
from cte
group by diff
having count(diff)>3)
select id, visit_date, no_of_people
from cte
where diff in (select diff from cte_2)
Hi Ankit...Thanks for the content...My approach
WITH CTE AS(
SELECT *,id-(DENSE_RANK()OVER(ORDER BY id)) AS rn
FROM stadium
WHERE no_of_people>=100),
CTE_2 AS(
SELECT *,COUNT(*)OVER(PARTITION BY rn ORDER BY rn) AS total_count
FROM CTE)
SELECT id,visit_date,total_count
FROM CTE_2
WHERE total_count>=3
Perfect!!
Thanks for the video, here is my solution. Learnt the concept from one of your previous videos.
WITH CTE AS (SELECT S.*,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as prev_day_count,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as curr_day_count,
SUM(CASE WHEN people >= 100 THEN 1 ELSE 0 END) OVER(ORDER BY visit_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as next_day_count
FROM Stadium S)
# SELECT * FROM CTE;
SELECT id, visit_date, people FROM CTE
WHERE curr_day_count >= 3 OR prev_day_count >= 3 OR next_day_count >= 3
ORDER BY visit_date ASC;
Awesome 😎
Cheating
with group_data as (
select *,sum(if(no_of_people100
)
select * from group_data where cnt_consecutive>=3
easiest one
select id,visit_date,no_of_people from (
select *,
case when min(no_of_people) over(order by id range between current row and 2 following) > 100 then 1 else 0
end as flag
from stadium)s
where flag=1
Before I start the video…
Thank you for making it .
So nice of you ☺️
@@ankitbansal6 have been following you on linked In as well.. you and some other folks are doin a commendable job by sharing your knowledge … please continue to do so😍
with cte as (
select *,row_number() over(order by id) as idd
from (
select *,
lag(visit_date,1) over(order by visit_date) as nxt_date
from stadium )a
where no_of_people>100 and visit_date>nxt_date
)
Select id,visit_date,no_of_people from cte
where id-idd>1
Howz the approach brother ??
All you need is a lag function then minus current date from previous date and just take which is= to 1 day on the cte with first filter where no_of_people >=100
Your taught this approach in one of the previous video thatswhy after saw this question this logic immediately strike into my mind
with cte as(
select id,visit_date,no_of_people as curr_row,lag(no_of_people) over(order by visit_date asc) as prev_row,
lag(no_of_people,2) over(order by visit_date) as prev_two_row,
lead(no_of_people) over(order by visit_date) as next_row,
lead(no_of_people,2) over(order by visit_date) as next_two_row
from stadium)
select visit_date,curr_row as no_of_people
from cte
where (curr_row>=100 and next_row>=100 and next_two_row>=100) or
(curr_row>=100 and prev_row>=100 and next_row>=100) or
(curr_row>=100 and prev_row>=100 and prev_two_row>=100)
Awesome
Great video as always..
here's my take on the solution:
=========================================================
with cte as (select *,
case when no_of_people =100 then 1 else 0 end) over (order by id)) end as indicator
from stadium)
select id, visit_date, no_of_people
from (Select *, count(indicator) over(partition by indicator) cnt from cte) sq1
where cnt>=3 and indicator0
==========================================================
you sir are a genius
greetings from colombia
thank u for showing us solution on consecutive prblems
With cte as (
Select stadium *, case
When people>100 and
(
lead(people,1) over( order by I'd)>100 and
lead(people,2) over(order by I'd) >100
Or lag(people,1) over( order by I'd)>100 and
Lag(people,2) over(order by I'd)>100
) Then 1 else 0 end as newcol )
Select I'd, visitdate,people from cte where newcol=1;
select A.id, A.visit_date, A.no_of_people from(
select *,
sum(no_of_people) over(order by visit_date rows between 2 preceding and current row) as moving_3_days_sum
from stadium) as A
where A.moving_3_days_sum/3>100 and A.no_of_people>100;
My approach :
with cte as (
select *
,(case when no_of_people>=100 then 1 else 0 end) as letssee
from stadium), cte2 as (
select *,
(case when (lead(letssee) over(order by visit_date)=1 and lead(letssee,2) over(order by visit_date)=1
and letssee =1) or
(lead(letssee) over(order by visit_date)=1 and letssee=1 and lag(letssee) over(order by visit_date)=1)
or
(letssee=1 and lag(letssee) over(order by visit_date)=1 and lag(letssee,2) over(order by visit_date)=1)
then 1 else 0 end) as tryy
from cte)
select * from cte2 where tryy=1
Hi Ankt,this is also learnt from your other video:
with cte2 as (
with cte as(
select *,(case when no_of_people>100 then 1 else 0 end) as flag
from stadium)
select *,sum(flag) over(rows between current row and 2 following) as next3
from cte)
select distinct s.id,s.visit_date,s.no_of_people
from cte2 c2
join stadium s
on c2.next3=3 and (s.id=c2.id or s.id=c2.id+1 or s.id=c2.id+2 );
It will not Work
@@Vishal_19 it had.
with cte as (select *,lead(no_of_people,1,no_of_people) over(order by visit_date) as lead1,lead(no_of_people,2,no_of_people) over(order by visit_date) as lead2
from stadium)
select * from cte where no_of_people>100 and lead1>100 and lead2>100
How about this?
Thanks sir for this great solution....
My Solution using LEAD and LAG Window Function
select id, visit_date,no_of_people from(select *, case
when no_of_people >= 100 and lead(no_of_people) over()>100 and lead(no_of_people,2) over()>100 and lead(no_of_people,3) over()>100 then 1
when no_of_people >= 100 and lag(no_of_people,1) over()>100 and lead(no_of_people,1) over()>100 and lead(no_of_people,2) over()>100 then 1
when no_of_people >= 100 and lag(no_of_people,2) over()>100 and lag(no_of_people,1) over()>100 and lead(no_of_people,1) over()>100 then 1
when no_of_people >= 100 and lag(no_of_people,1) over()>100 and lag(no_of_people,2) over()>100 and lag(no_of_people,3) over()>100 then 1
end as noofpeople from stadium) x where x.noofpeople0;
Similar Approach:
with cte AS(
select
*, count(1) over(partition by diff) as tot_cnt
from (
select
*,
id - ROW_NUMBER() over(order by id) as diff
from stadium
where no_of_people > 100
)A
)
select * from stadium
where id in (
select id from cte where tot_cnt >= 3
)
Hi, Could you please cover other skill of Data engineer as well like Data modelling, DM , and core skills which need for a Data Engineer profile to clear interviews for experienced people?
Yes
Yes please
much needed!
My approach
WITH CTE AS (
SELECT * ,
LEAD(NO_OF_PEOPLE,1,101) OVER(ORDER BY VISIT_DATE) AS R1,
LEAD(NO_OF_PEOPLE,2,101) OVER(ORDER BY VISIT_DATE) AS R2
FROM STADIUM
)
SELECT ID,VISIT_DATE,NO_OF_PEOPLE
FROM CTE
WHERE NO_OF_PEOPLE >100 AND R1>100 AND R2>100
with cte as
(
Select *, row_number() over(order by id) as rn from stadium where no_of_people>100
)
Select id,(case when id-rn=2 then visit_date end) as visit, no_of_people from cte where (case when id-rn=2 then visit_date end) is not null
Hello ankit can you please tell me which palylist i follow for data engineer interview , please share the link below.
Complex SQL Questions for Interview Preparation: th-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
with cte as (select * ,
lead(people) over(order by visit_date) as ld,
lag(people) over(order by visit_date) as lg,
lead(people, 2) over(order by visit_date) as ld1,
lag(people, 2) over(order by visit_date) as lg1
from stadium)
select id, visit_date, people
from cte
where
(people >= 100 and ld >= 100 and ld1 >= 100) or
(people >= 100 and lg >= 100 and ld >= 100) or
(people >= 100 and lg >= 100 and lg1 >= 100)
not sure if this is faster or slower but definitely easier to write
Short and better solution, I used UNION when I did this query on Leetcode.
select id,visit_date,no_of_people from
(
select a.id,a.visit_date,a.no_of_people
from stadiums a inner join stadiums b
on a.id=b.id+1
and a.no_of_people>=100 and b.no_of_people>=100
inner join stadiums c
on a.id=c.id+2 and a.no_of_people>=100 and c.no_of_people>=100
union
select a.id,a.visit_date,a.no_of_people
from stadiums a inner join stadiums b
on a.id=b.id-1
and a.no_of_people>=100 and b.no_of_people>=100
inner join stadiums c
on a.id=c.id-2 and a.no_of_people>=100 and c.no_of_people>=100
union
select a.id,a.visit_date,a.no_of_people
from stadiums a inner join stadiums b
on a.id=b.id-1
and a.no_of_people>=100 and b.no_of_people>=100
inner join stadiums c
on a.id=c.id+1 and a.no_of_people>=100 and c.no_of_people>=100
) A
order by id;
👍
with cte as (select *, ifnull(lead(no_of_people,1) over(order by visit_date),101) as lg1,
ifnull(lead(no_of_people,2) over(order by visit_date),101) as lg2
from stadium)
select id, visit_date,no_of_people from cte where no_of_people>100 and lg1>100 and lg2>100
with cte as (SELECT *, isnull(min(no_of_people) over
(order by visit_date rows between 1 following and 2 following), no_of_people) as ppl_cons from stadium)
SELECT * from cte where no_of_people >=100 and ppl_cons>=100
;with cte as (
select *,
case when no_of_people>100 and
LEAD(no_of_people)over(order by visit_date) >100
and LEAD(no_of_people,2)over(order by visit_date) >100
then 1
when no_of_people>100 and
Lag(no_of_people)over(order by visit_date) >100
and Lag(no_of_people,2)over(order by visit_date) >100
then 1 else 0
end as flag
from stadium )
select * from cte where flag=1
Hi , ankit this is my solution
with cte1 as(select* ,ntile(2)over(order by (select null)) as grp
from stadium)
,cte2 as(select *
from cte1
where no_of_people>100)
select *
from cte2
where grp in(select grp
from cte2
group by grp
having count(grp)>=3)
with ranges as (
select min(visit_date) start,max(visit_date) end
from(
select * ,
id-row_number() over(partition by diff order by visit_date) as grp
from (
select * ,
case when people >100 then 1 else 0 end as diff
from stadium
) a)
b where people >100
group by grp
having datediff(end,start) >=3
)
select s.* from stadium s join ranges r where s.visit_date between r.start and r.end
can we use below query
....................................
with cte as (
select *,Lead(no_of_people,1) over(order by visit_date) ln1
,Lead(no_of_people,2) over(order by visit_date)ln2
,Lead(no_of_people,3) over(order by visit_date)ln3
from stadium
)
select * from cte where ln1>100 and ln2>100 and ln3>100
;
hey ankit,
pls make me understand the data, is it assumed that data is only having cases with consecutive ids and greater than 100?
because there could be another possibilty where consecutive ids condition is not met but greater than 100 is satisfied
this is the best soln and short
Hi Ankit, thanks for posting. Here is my solution: WITH consecutive_increments AS (
SELECT
id,
visit_date,
no_of_people,
LEAD(visit_date, 1, 0) OVER (ORDER BY visit_date) AS second_date,
LEAD(no_of_people, 1, 0) OVER (ORDER BY visit_date) AS second_no_of_people,
LEAD(visit_date, 2, 0) OVER (ORDER BY visit_date) AS third_date,
LEAD(no_of_people, 2, 0) OVER (ORDER BY visit_date) AS third_no_of_people
FROM stadium
), consecutive_increments_2 AS (
SELECT
visit_date,
second_date,
third_date,
no_of_people,
second_no_of_people,
third_no_of_people
FROM consecutive_increments
WHERE no_of_people >= 100
AND second_no_of_people >= 100
AND third_no_of_people >= 100
)
SELECT
visit_date,
no_of_people
FROM consecutive_increments_2
UNION
SELECT
second_date AS visit_date,
second_no_of_people
FROM consecutive_increments_2
UNION
SELECT
third_date AS visit_date,
third_no_of_people
FROM consecutive_increments_2
ORDER BY visit_date;
with cte1 as(
select *,row_number() over(order by visit_date) as rn,
id-row_number() over(order by visit_date) as grp
from stadium where no_of_people>=100
),cte2 as(
select count(id) as cnt,grp from cte1 group by grp having count(1)>=3
)
select * from cte1 where grp in(select grp from cte2)
MYSQL Solution
with base as
(select *,day(date_sub(visit_date,interval row_number() over() day)) as diff
from stadium where no_of_people >=100),
base_cnt as (
select *,count(diff) over(partition by diff) as cnt from base)
select id,visit_date,no_of_people from base_cnt where cnt >=3
Select * from stadium
for consective question -> different approaches
a. lead1, lead2
b. lead1, lead2, lag1,lag2
c. sum(case) 2 preceding and current row as Prev_2, , 1 preceding and 1 following) as Prev_next_1 , current row and 2 following) as next_2
d. ntile - groupuing
d. dense_rank -> ,id-(DENSE_RANK()OVER(ORDER BY id))
e. ankit -> row_number & id-rn
;with cte as
(
Select *, ROW_NUMBER() over(order by visit_date) as rn, id-ROW_NUMBER() over(order by visit_date) as grp
from stadium
where no_of_people>=100
)
select * from cte
where grp in (select grp from cte
group by grp
having count(*)>=3
)
What if there is gap between the days?
why did you write order_date in the row_number instead of id because the difference would be the same.
my solution:
with cte as (
select *
,isnull(lead(no_of_people,1) over (order by id),LAG(no_of_people,1) over(order by id)) as p1
,isnull(lead(no_of_people,2) over (order by id),LAG(no_of_people,2) over(order by id)) as p2
from
stadium
),
cte2 as (
select *
,case
when no_of_people>=100 and p1>=100 and p2>=100 then 1 else 0 end as mrk
from cte
)
select id,visit_date,no_of_people
from cte2
where mrk=1
Is this approach to the problem correct? Please find my solution below :
Select id,visit_date,no_of_people
from (Select *,
SUM(temp.flag) Over(order by id) as running_sum
from (Select * ,
CASE
WHEN no_of_people > 100 THEN 1
ELSE 0
END as flag
from stadium) temp) final_table
where final_table.running_sum >= 3;
Please help😅
WITH cte AS (
SELECT id
FROM Stadium
WHERE people > 99
)
SELECT *
FROM Stadium
WHERE people > 99 AND ((id-1 IN (TABLE cte) AND id-2 IN (TABLE cte))
OR (id+1 IN (TABLE cte) AND id+2 IN (TABLE cte))
OR (id+1 IN (TABLE cte) AND id-1 IN (TABLE cte)))
with cte as (select id,visit_date,no_of_people,ROW_NUMBER() over (order by (select 1)) r,
id-ROW_NUMBER() over (order by (select 1)) x
from stadium where no_of_people>=100),
cte2 as (
select *,count(1) over (partition by x order by (select 1)) y from cte)
select id,visit_date,no_of_people from cte2 where y>=3
with cte as(Select *, lead(no_of_people, 1) over (order by visit_date) as lead_1, lead(no_of_people, 2) over (order by visit_date) as lead_2,
lag(no_of_people, 1) over (order by visit_date) as lag_1, lag(no_of_people, 2) over (order by visit_date) as lag_2 from stadium)
Select id, visit_date, no_of_people from (Select *, case when no_of_people> 99 and lead_1> 99 AND lead_2>99 then 'Y'
when no_of_people> 99 and lag_1> 99 AND lag_2>99 then 'Y' End as Status from cte) where Status =='Y'
WITH CTE AS(
select s.*,
dense_rank()over(order by visit_date) as rank,
id-dense_rank()over(order by visit_date)as grp
from stadium s
where no_of_people>=100
)
SELECT ID,VISIT_DATE,NO_OF_PEOPLE
FROM CTE WHERE GRP>=2
;
IS IT FINE?
Great Explanation 👍
select id,visit_date,no_of_people from(
select * ,count(*) over(partition by rd) as ct from
(select * , (id - row_number( )over(order by id )) as rd from stadium where no_of_people >= 100))
where ct>= 3;
why you use 'in' in the last ,we can use 'exists' also
in is slower
-- with frame clause in windows function
with cte as(select *,
case when no_of_people>=100 then 1 else 0 end,
sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between 2 preceding and current row) as previous_2d_sum,
sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between 1 preceding and 1 following) as 1_up_down,
sum(case when no_of_people>=100 then 1 else 0 end) over(order by visit_date rows between current row and 2 following) as current_2_down
from stadium)
select id,visit_date,no_of_people from cte where previous_2d_sum=3 or 1_up_down=3 or current_2_down=3;
select * from (
select *
,lag(no_of_people,1) over (order by visit_date ) as prev_1
,lag(no_of_people,2,0) over (order by visit_date ) as prev_2
,lead(no_of_people,1,0) over (order by visit_date ) as next_1
,lead(no_of_people,2,0) over (order by visit_date ) as next_2
from stadium)A
where (no_of_people>100 and prev_1>100 and prev_2>100)
or (no_of_people>100 and next_1>100 and next_2>100)
or (no_of_people>100 and prev_1>100 and next_1>100)
I tried differently;
with cte as(
select *,
(case when (no_of_people >= 100 and
lead(no_of_people, 1, null) over(order by id) >=100
and lead(no_of_people, 2, null) over(order by id) >= 100)
then 1 else 0 end) ld,
(case when (no_of_people >= 100 and
lag(no_of_people, 1, null) over(order by id) >=100
and lag(no_of_people, 2, null) over(order by id) >= 100)
then 1 else 0 end) lg
from stadium )
select * from cte where ld = 1 or lg =1;
with prev_count as (select *,lag(no_of_people,1,0) over(order by id) as prev_1 ,lag(no_of_people,2,0) over(order by id) as prev_2, lead(no_of_people,1,0) over(order by id) as next_1,
lead(no_of_people,2,0) over(order by id) as next_2 from stadium),final as (select *,
case when no_of_people>=100 and prev_1 >= 100 and prev_2>= 100 then "Y"
when no_of_people>=100 and prev_1 >= 100 and next_1 >= 100 then "Y"
when no_of_people>=100 and next_1 >= 100 and next_2 >= 100 then "Y"
else "N" end as 'status'
from prev_count) select id,visit_date,no_of_people from final where status = 'Y';
or
with cte as (select * from stadium where no_of_people >= 100),
cte2 as (select *, id-row_number() over (order by id) as rnk from cte)
select id,visit_date,no_of_people from cte2 where rnk in (select rnk from cte2 group by 1 having count(rnk) >= 3)
with stadium_cte as(
select *
,ROW_NUMBER() over(order by visit_date) as rno
from stadium where no_of_people>=100),
count_cte as(
select id,
visit_date,
no_of_people,
id-rno as diff from stadium_cte),
final_cte as(
select diff,count(diff) as count
from count_cte
group by diff
having count(diff)>=3)
select id,visit_date,no_of_people from count_cte c1 inner join final_cte c2 on c1.diff=c2.diff
select * from stadium
where id in (
with cte as(
select id, lag(id,1) over (order by id) as id1,lag(id,2) over (order by id) as id2,
visit_date,no_of_people, lag(no_of_people,1) over (order by id) as p1,
lag(no_of_people,2) over (order by id) as p2
from stadium order by visit_date asc)
select id from cte where
no_of_people > 100 and p1 >100 and p2 >100
union select id1 from cte where
no_of_people > 100 and p1 >100 and p2 >100
union select id2 from cte where
no_of_people > 100 and p1 >100 and p2 >100)
That's brilliant bro.
Glad you liked it
with forward_consecutive as (
select * ,
lead(no_of_people,1) over(order by visit_date ) as day2,
lead(no_of_people,2) over(order by visit_date) as day3
from stadium ),
backward_consecutive as (
select * ,
lag(no_of_people,1) over(order by visit_date ) as day2,
lag(no_of_people,2) over(order by visit_date) as day3
from stadium )
select * from (select * from backward_consecutive union all select * from forward_consecutive) aa
where no_of_people >100 and day2>100 and day3>100
;
Hi Ankit please check and verity
;with cte as(
select *,
case when no_of_people>=100 then 1 else 0 end as flag
from stadium)
,cte2 as(
select *,ROW_NUMBER() over (partition by flag order by visit_date) rn
,dateadd(day,-1*ROW_NUMBER() over (partition by flag order by visit_date) ,visit_date) grp
from cte)
select id,visit_date,no_of_people from cte2 where flag=1 and grp in (select grp from cte2
group by flag,grp
having count(9)>=3 and flag=1)
You are awesome
without using id column :
with cte as (select *,
lag(visit_date) over( order by visit_date) as first_date,
lag(visit_date,2) over( order by visit_date) as second_date,
lag(visit_date,3) over( order by visit_date) as third_date
from stadium),
cte2 as (
select
case
when visit_date-first_date=1 and visit_date-second_date=2 and visit_date-third_date=3
then id end as win
from cte)
select a.id,a.visit_date,a.no_of_people from cte a join cte2 b on
a.id=b.win
where a.no_of_people>100
Good
By doing this code, it also gives us the same result. Can you please check here:
(
select *,
row_number() over(order by id) as rnk,
id-row_number() over(order by id) as grp
from stadium
where no_of_people >=100
)
select *
from grp_num
where grp>=2
this one is not generic suppose if you grp column gets another value as 3 but that is only 2 times that time this will not work
with CTE as (select *,
min(no_of_people) over(order by visit_date rows between 1 following and 1 following) as nextday,
min(no_of_people) over(order by visit_date rows between 2 following and 2 following) as nexttonextday
from stadium
)
select id, visit_date,no_of_people from (select *,
case when no_of_people >100 and nextday>100 and nexttonextday>100 then 'TRUE'
when no_of_people >100 and nextday>100 and nexttonextday is NULL then 'TRUE'
when no_of_people >100 and nextday is NULL and nexttonextday is NULL then 'TRUE'
else 'FALSE' end as output from CTE )A where output='TRUE'
hey ankit, your solution is not working if there is all amount people rows is grater than 100
--Leetcode Hard SQL Problem | Human Traffic of Stadium
with cte as (select
id,
sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between 2 preceding and current row) two_pred,
sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between 1 preceding and 1 following) two_pred_next,
sum(case when no_of_people >= 100 then 1 else 0 end) over (order by visit_date rows between current row and 2 following) two_next
from
stadium
)
select id from cte
where two_pred = 3 or two_pred_next = 3 or two_next = 3
there is one problem now the id is consecutive but the day are not consecutive
with cts as (select *,
row_number() over(order by id) as rownum,
id-ROW_NUMBER() over(order by id) as diff
from dbo.stadium
where no_of_people >= 100)
I am trying to use the above query and I am getting teh below error.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ';'.
Can someone help me to resolve this?
with cte1 as
(select *,lag(indicator,1,indicator) over(order by visit_date) i2 from
(select *,case when no_of_people>=100 then "y" else "n" end indicator from stadium) a),
cte2 as
(select *,sum(case when indicator="n" and i2="y" then 1 else 0 end) over(order by visit_date) grp_key
from cte1),
cte3 as
(select min(visit_date)k_d,max(visit_date) m_d,grp_key from
cte2 group by grp_key having count(*)>=4)
select visit_date,no_of_people from cte3 join cte2 using (grp_key)
where visit_date between k_d and m_d having no_of_people>=100
(my hard answer !)
select id,visit_date,people from(select *,
case when
people>=100 and
((lag(people) over(order by id)>=100 and
lag(people,2) over(order by id)>=100) or
(lead(people) over(order by id)>=100 and
lead(people,2) over(order by id)>=100) or
(lag(people) over(order by id)>=100 and
lead(people) over(order by id)>=100))
then 1
else 0
end as flag
from stadium) res
where flag=1
with cte as
(select id, visit_date , no_of_people
, lead(no_of_people,1,no_of_people) over (order by visit_date) as ln
, lead(no_of_people,2,no_of_people) over (order by visit_date) as ln2
from stadium)
select id, visit_date , no_of_people
from cte
where no_of_people >= 100 and ln >= 100 and ln2 >= 100
with people_cte as(
select * from stadium where no_of_people >=100),
people_cte_two as (
select *,
isnull(lead(id,1) over(order by id),0) as second_num
,isnull(lead(id,2) over(order by id),0) as third_num
from people_cte),
third_cte as(
select *
,second_num-id as first_diff
,third_num-second_num as second_diff
from people_cte_two)
select id,visit_date,no_of_people from third_cte where first_diff=1 and second_diff=1 or second_diff=0 or second_diff
Looks good 😊
@@ankitbansal6 Bro when are you planning to start the Python series
My Solution:
-- select * from Stadium;
with CTE AS (
SELECT *,
lead(people,1) over(order by visit_date) AS lead_1st_day,
lead(people,2) over(order by visit_date) AS lead_2nd_day,
lag(people,1) over(order by visit_date) AS lag_1st_day,
lag(people,2) over(order by visit_date) AS lag_2nd_day
FROM STADIUM )
-- Select * from CTE;
SELECT
id,
visit_date,
people
FROM CTE
WHERE
(people >= 100 AND lead_1st_day >= 100 AND lead_2nd_day >= 100) OR
(people >= 100 AND lead_1st_day >= 100 AND lag_1st_day >= 100) OR
(people >= 100 AND lag_1st_day >= 100 AND lag_2nd_day >= 100)
ORDER BY visit_date;
How about this sir:
with t as(select id, visit_date,no_of_people,lag(no_of_people, 1) over (order by visit_date)lag1,lag(no_of_people, 2) over (order by visit_date)lag2
,lead(no_of_people, 1) over (order by visit_date)lead1,lead(no_of_people, 2) over (order by visit_date)lead2
from stadium
)
select id,visit_Date,no_of_people
from t
where no_of_people >= 100 and (lead1 >= 100 and lead2 >= 100 or (lag1 >= 100 and lag2 >=100))
with cte AS
(
select * from stadium
where no_of_people >= 100
),ctf AS
(
select *,lead(id,1,id) over(order by id) as nxt_id,
(lead(id,1,id) over(order by id) - id) as diff
from cte
),ctk AS
(
select *,
row_number() over(order by id) as rn,
(id - row_number() over(order by id)) as diff_new
from ctf
where diff in (1,0)
)
select id,visit_date,no_of_people
from ctk JOIN
(
select diff_new from ctk
group by diff_new having count(*) >= 3
)A
on ctk.diff_new = a.diff_new
WITH
cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY id ASC) AS rnk1 FROM stadium
ORDER BY id
)
,
cte2 AS
(
SELECT *, ROW_NUMBER() OVER(order by id) AS rnk2, rnk1 - ROW_NUMBER() OVER(order by id) as key
FROM cte
WHERE no_of_people>=100
)
,
cte3 AS
(
SELECT *, COUNT(1) OVER(PARTITION BY key) FROM cte2
)
SELECT id,visit_date,no_of_people FROM cte3
WHERE count>=3
🥰
Hi, can I please have the leetcode number for this problem
601
@@ankitbansal6 Thank you, a suggestion would be to include the leetcode number in the title as many search based on that and thx for the solid content!!
WITH cte AS (SELECT *,rank() OVER(ORDER BY visit_date) AS rnk,id-rank() OVER(ORDER BY visit_date) AS grp
FROM stadium WHERE no_of_people>100)
SELECT * FROM cte WHERE grp IN (SELECT grp FROM cte GROUP BY grp HAVING count(1)>3 )
WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER(ORDER BY visit_date ASC) AS rn
FROM
stadium
WHERE
no_of_people >= 100
)
SELECT * FROM cte WHERE id-rn = 2
;
select id,visit_Date,no_of_people from
(select id,visit_Date,no_of_people ,count(*) over(partition by cnt ) cnt1 from
(select id,visit_Date,no_of_people,id -rownum cnt from stadium where no_of_people >100) a)
where cnt1 >=3
order by id;
WITH Consecutive_days AS (
SELECT
id, visit_date, no_of_people,
CASE
WHEN no_of_people > 100 AND LAG(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LAG(no_of_people, 2) OVER (ORDER BY visit_date) > 100 THEN 1
WHEN no_of_people > 100 AND (LAG(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LEAD(no_of_people, 1) OVER (ORDER BY visit_date) > 100) THEN 1
WHEN no_of_people > 100 AND LEAD(no_of_people, 1) OVER (ORDER BY visit_date) > 100 AND LEAD(no_of_people, 2) OVER (ORDER BY visit_date) > 100 THEN 1
ELSE 0
END AS is_consecutive
FROM stadium
)
SELECT
id, visit_date, no_of_people
FROM Consecutive_days
WHERE
is_consecutive = 1
ORDER BY
visit_date;
WITH cte AS (
SELECT id, visit_date, no_of_people, COUNT(rn) OVER (PARTITION BY rn) as cnt_flag
FROM (
SELECT * ,id - ROW_NUMBER() OVER (ORDER BY visit_date) AS rn
FROM stadium
WHERE no_of_people>=100) T)
SELECT id, visit_date, no_of_people
FROM cte
WHERE cnt_flag>=3
with cte as (
select *,
ROW_NUMBER()OVER(ORDER BY visit_date) as rw_num,
id - ROW_NUMBER()OVER(ORDER BY visit_date) as grp
from stadium
where no_of_people >= 100),
cte2 AS (
select grp,COUNT(no_of_people) as Num
FROM CTE
group by grp
HAVING COUNT(no_of_people) >= 3)
Select id,visit_date,no_of_people
from cte as c inner join cte2 as c1 on c.grp = c1.grp