Leetcode Hard SQL Problem | Human Traffic of Stadium

แชร์
ฝัง
  • เผยแพร่เมื่อ 7 พ.ย. 2024

ความคิดเห็น • 139

  • @puneetnaik8719
    @puneetnaik8719 2 ปีที่แล้ว +11

    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
      @ankitbansal6  2 ปีที่แล้ว +1

      Thank you 😊

    • @litheshraju5207
      @litheshraju5207 2 ปีที่แล้ว +1

      great way to optimise

    • @anubhavsahay8116
      @anubhavsahay8116 2 ปีที่แล้ว

      @@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

    • @sreesennu
      @sreesennu 2 ปีที่แล้ว +1

      @@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

    • @Thetradersclub_
      @Thetradersclub_ 6 หลายเดือนก่อน

      Super approach 🔥

  • @shekharagarwal1004
    @shekharagarwal1004 2 ปีที่แล้ว +6

    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)
    );

  • @mohammedriyaz9082
    @mohammedriyaz9082 2 ปีที่แล้ว +1

    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

  • @sahilummat8555
    @sahilummat8555 13 วันที่ผ่านมา

    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

  • @swapnilshimpi7588
    @swapnilshimpi7588 2 ปีที่แล้ว +2

    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;

    • @KapilKumar-hk9xk
      @KapilKumar-hk9xk 5 หลายเดือนก่อน +1

      This one is most simple and straightforward solution. Thanks.

  • @rahulkushwaha6469
    @rahulkushwaha6469 2 ปีที่แล้ว +2

    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.

  • @manaskukreja
    @manaskukreja 2 ปีที่แล้ว +3

    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

  • @GaneshNaduvathati
    @GaneshNaduvathati ปีที่แล้ว

    ;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.

  • @KoushikT
    @KoushikT 2 ปีที่แล้ว +1

    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

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว

      What is interval here ?

    • @KoushikT
      @KoushikT 2 ปีที่แล้ว

      The row number calculated in cte A acts as the interval in the dateadd function

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว

      I don't think this is SQL server. Which database you are using?

    • @KoushikT
      @KoushikT 2 ปีที่แล้ว

      I am using MySQL

  • @akash4517
    @akash4517 2 ปีที่แล้ว +1

    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

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว

      You missed 1 preceding and 1 following

  • @Tusharchitrakar
    @Tusharchitrakar ปีที่แล้ว +1

    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;

  • @dfkgjdflkg
    @dfkgjdflkg 10 หลายเดือนก่อน

    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

    • @ankitbansal6
      @ankitbansal6  10 หลายเดือนก่อน

      Thanks for watching!

  • @mantisbrains
    @mantisbrains หลายเดือนก่อน

    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 !

  • @MixedUploader
    @MixedUploader 10 หลายเดือนก่อน

    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

    • @maniarasand3579
      @maniarasand3579 9 หลายเดือนก่อน

      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.

  • @mr.pingpong502
    @mr.pingpong502 4 หลายเดือนก่อน

    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

  • @ayushgupta3097
    @ayushgupta3097 ปีที่แล้ว +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

  • @litheshraju5207
    @litheshraju5207 2 ปีที่แล้ว +1

    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

  • @arpitagarwal8485
    @arpitagarwal8485 2 ปีที่แล้ว

    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.

  • @utkarshsingh2462
    @utkarshsingh2462 2 ปีที่แล้ว +2

    Great video on Gaps & Islands problem, Thank you Ankit!

  • @Chathur732
    @Chathur732 3 หลายเดือนก่อน

    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)

  • @mohdtoufique7446
    @mohdtoufique7446 2 ปีที่แล้ว +6

    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

  • @praveensinghrathore4542
    @praveensinghrathore4542 2 ปีที่แล้ว +1

    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;

  • @skkholiya
    @skkholiya 18 วันที่ผ่านมา

    with group_data as (
    select *,sum(if(no_of_people100
    )
    select * from group_data where cnt_consecutive>=3

  • @SudhirSingh-l3p
    @SudhirSingh-l3p ปีที่แล้ว

    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

  • @kashmirshadows8150
    @kashmirshadows8150 2 ปีที่แล้ว

    Before I start the video…
    Thank you for making it .

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว

      So nice of you ☺️

    • @kashmirshadows8150
      @kashmirshadows8150 2 ปีที่แล้ว +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😍

  • @indergaming3053
    @indergaming3053 ปีที่แล้ว

    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 ??

  • @souhaielmensi
    @souhaielmensi ปีที่แล้ว

    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

  • @aaravkumarsingh4018
    @aaravkumarsingh4018 2 ปีที่แล้ว

    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)

  • @varunas9784
    @varunas9784 4 หลายเดือนก่อน

    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
    ==========================================================

  • @diegogonzalez3562
    @diegogonzalez3562 ปีที่แล้ว

    you sir are a genius
    greetings from colombia

  • @Wasimd-t4b
    @Wasimd-t4b 9 หลายเดือนก่อน

    thank u for showing us solution on consecutive prblems

  • @zaravind4293
    @zaravind4293 2 ปีที่แล้ว +1

    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;

  • @PrabhatKumar-xq1yk
    @PrabhatKumar-xq1yk ปีที่แล้ว

    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;

  • @oorvimathur9807
    @oorvimathur9807 ปีที่แล้ว

    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

  • @rashmidutta7151
    @rashmidutta7151 2 ปีที่แล้ว

    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 );

  • @armanmardhani8963
    @armanmardhani8963 ปีที่แล้ว +1

    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?

  • @mranaljadhav8259
    @mranaljadhav8259 ปีที่แล้ว

    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;

  • @Maharanasunil01
    @Maharanasunil01 7 หลายเดือนก่อน

    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
    )

  • @india6080
    @india6080 2 ปีที่แล้ว +7

    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?

  • @aravindsastry8895
    @aravindsastry8895 20 วันที่ผ่านมา

    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

  • @sahilrohilla8796
    @sahilrohilla8796 2 ปีที่แล้ว

    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

  • @raviyadav-dt1tb
    @raviyadav-dt1tb 5 หลายเดือนก่อน

    Hello ankit can you please tell me which palylist i follow for data engineer interview , please share the link below.

    • @ankitbansal6
      @ankitbansal6  5 หลายเดือนก่อน +1

      Complex SQL Questions for Interview Preparation: th-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html

  • @prajwaltidke1435
    @prajwaltidke1435 ปีที่แล้ว

    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

  • @vivekkumarsingh9009
    @vivekkumarsingh9009 2 ปีที่แล้ว +2

    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;

  • @gautambhandare8164
    @gautambhandare8164 9 หลายเดือนก่อน

    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

  • @onwardsandupwards11
    @onwardsandupwards11 ปีที่แล้ว

    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

  • @sahilummat8555
    @sahilummat8555 5 หลายเดือนก่อน

    ;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

  • @dasoumya
    @dasoumya 2 ปีที่แล้ว

    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)

  • @mission10000
    @mission10000 ปีที่แล้ว

    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

  • @amanahmed6057
    @amanahmed6057 2 ปีที่แล้ว

    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
    ;

  • @kashishlohiya6170
    @kashishlohiya6170 2 ปีที่แล้ว

    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

  • @coding7241
    @coding7241 ปีที่แล้ว

    this is the best soln and short

  • @tahakhalid324
    @tahakhalid324 20 วันที่ผ่านมา

    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;

  • @rajunaik8803
    @rajunaik8803 2 ปีที่แล้ว

    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)

  • @anirvansen5024
    @anirvansen5024 2 ปีที่แล้ว

    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

  • @AkashKumar-mb4pd
    @AkashKumar-mb4pd 10 หลายเดือนก่อน

    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
    )

  • @divit00
    @divit00 7 หลายเดือนก่อน

    What if there is gap between the days?

  • @tanushreepareek8208
    @tanushreepareek8208 ปีที่แล้ว

    why did you write order_date in the row_number instead of id because the difference would be the same.

  • @hamzakazmi5150
    @hamzakazmi5150 2 ปีที่แล้ว

    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

  • @deependrarajsingh9983
    @deependrarajsingh9983 8 หลายเดือนก่อน

    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😅

  • @saisreekar7414
    @saisreekar7414 11 หลายเดือนก่อน

    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)))

  • @shankarpesala921
    @shankarpesala921 ปีที่แล้ว

    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

  • @sumitsingh-bi7ls
    @sumitsingh-bi7ls 2 ปีที่แล้ว

    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'

  • @sauravbhattacharjee6297
    @sauravbhattacharjee6297 ปีที่แล้ว

    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?

  • @sivamukku5058
    @sivamukku5058 2 ปีที่แล้ว

    Great Explanation 👍

  • @ujjwalvarshney3188
    @ujjwalvarshney3188 ปีที่แล้ว

    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;

  • @amanahmed6057
    @amanahmed6057 2 ปีที่แล้ว

    why you use 'in' in the last ,we can use 'exists' also
    in is slower

  • @ShivamGupta-wn9mo
    @ShivamGupta-wn9mo หลายเดือนก่อน

    -- 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;

  • @SachinGupta-nh5vy
    @SachinGupta-nh5vy ปีที่แล้ว

    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)

  • @ramakumarguntamadugu1299
    @ramakumarguntamadugu1299 ปีที่แล้ว

    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;

  • @anish_bhateja
    @anish_bhateja ปีที่แล้ว

    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)

  • @adharshsunny9523
    @adharshsunny9523 2 ปีที่แล้ว

    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

  • @ASHISH517098
    @ASHISH517098 ปีที่แล้ว

    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)

  • @jackyychn7053
    @jackyychn7053 ปีที่แล้ว

    That's brilliant bro.

  • @pratiksingh9480
    @pratiksingh9480 ปีที่แล้ว

    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
    ;

  • @AravindKumar-es4nd
    @AravindKumar-es4nd ปีที่แล้ว

    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)

  • @innominatesoloist1597
    @innominatesoloist1597 2 ปีที่แล้ว

    You are awesome

  • @playtrip7528
    @playtrip7528 2 ปีที่แล้ว

    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

  • @tanushreepareek8208
    @tanushreepareek8208 ปีที่แล้ว

    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

    • @TargaryenGaming-u3u
      @TargaryenGaming-u3u ปีที่แล้ว

      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

  • @naveennagar507
    @naveennagar507 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'

  • @ganeshkokil2471
    @ganeshkokil2471 2 ปีที่แล้ว

    hey ankit, your solution is not working if there is all amount people rows is grater than 100

  • @sadamayub419
    @sadamayub419 ปีที่แล้ว

    --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

  • @shadiqurrahaman6096
    @shadiqurrahaman6096 26 วันที่ผ่านมา

    there is one problem now the id is consecutive but the day are not consecutive

  • @nirmalkumarr2235
    @nirmalkumarr2235 ปีที่แล้ว

    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?

  • @Dhanushts-g7x
    @Dhanushts-g7x ปีที่แล้ว

    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 !)

  • @prashanthpk6901
    @prashanthpk6901 ปีที่แล้ว

    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

  • @greatromulus3028
    @greatromulus3028 ปีที่แล้ว

    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

  • @adharshsunny9523
    @adharshsunny9523 2 ปีที่แล้ว +1

    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

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว

      Looks good 😊

    • @as-youtuber6215
      @as-youtuber6215 2 ปีที่แล้ว

      @@ankitbansal6 Bro when are you planning to start the Python series

  • @shubhamgeorgian
    @shubhamgeorgian 2 หลายเดือนก่อน

    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;

  • @akshayjoshi4106
    @akshayjoshi4106 2 ปีที่แล้ว

    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))

  • @vivekdutta7131
    @vivekdutta7131 3 หลายเดือนก่อน

    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

  • @akelakela5524
    @akelakela5524 ปีที่แล้ว

    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

  • @shankrukulkarni3234
    @shankrukulkarni3234 ปีที่แล้ว

    🥰

  • @dilip_1997
    @dilip_1997 2 ปีที่แล้ว +1

    Hi, can I please have the leetcode number for this problem

    • @ankitbansal6
      @ankitbansal6  2 ปีที่แล้ว +1

      601

    • @dilip_1997
      @dilip_1997 2 ปีที่แล้ว

      @@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!!

  • @ZeeshanSyed-x5i
    @ZeeshanSyed-x5i 19 วันที่ผ่านมา

    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 )

  • @SuperMohit95
    @SuperMohit95 2 ปีที่แล้ว

    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
    ;

  • @amrutaborse2163
    @amrutaborse2163 9 หลายเดือนก่อน

    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;

  • @AyshathLubna-f2j
    @AyshathLubna-f2j 4 หลายเดือนก่อน

    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;

  • @edumail1016
    @edumail1016 2 หลายเดือนก่อน

    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

  • @pradeepd6090
    @pradeepd6090 26 วันที่ผ่านมา

    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