Human Traffic of Stadium


  • 0

    Click here to see the full article post


  • 0
    P

    select stadium.id, stadium.date, stadium.people from stadium,
    (

    		select s1.id as id1, s2.id as id2
    
    		from stadium s1, stadium s2
    		where s2.id  - s1.id >= 2
    		and s1.people >= 100 
    		and (
    			select max(s5.people) 
    			from stadium s5 
    			where s5.id = s1.id - 1 or s5.id = s2.id + 1
    		) < 100
    		and (
    			select min(s3.people) 
    			from stadium s3 
    			where s3.id >= s1.id 
    			and s3.id <= s2.id
    		) >=100
    

    ) s4
    where id >=s4.id1 and id <= s4.id2


  • 0
    E

    SELECT DISTINCT t1.id,t1.date,t1.people FROM stadium t1,stadium t2,stadium t3 WHERE
    t1.people>99
    AND t2.people>99
    AND t3.people>99
    AND (
    (t1.id=t2.id+1
    AND t1.id=t3.id+2)
    OR (t1.id=t2.id+1
    AND t1.id=t3.id-1)
    OR (t1.id=t2.id-1
    AND t1.id=t3.id-2)
    )
    ORDER BY t1.id;


  • 0
    Z

    i do not think this problem to described appropriately ,questions reveal that consecutive days,however,after 5-29,it seems to be 6-1 the next day.so ,we downgrade to check conditions as id.i don't think this a nice way


  • 0

    @zangguodong Is it a test case issue or what? Let me know so that I may correct/improve it, please.


  • 0
    Z

    Firstly,thank you for your note.
    It's my fault to misunderstand the question.Question puts that " 3 or more consecutive rows " ,not" 3 or more consecutive days ",so my ans:
    select distinct a.id,a.date,a.people from stadium a,stadium b,stadium c where a.people>=100
    and b.people>=100 and c.people>=100 and
    ((b.date=a.date+1 and c.date=a.date+2) or
    (b.date=a.date-1 and c.date=a.date+1) or
    (b.date=a.date-2 and c.date=a.date-1)) order by a.date
    cannot pass test when there be data:
    5-29 xxx(>100)
    6-1 xxx(>100)

    test case are ok,sorry again foo my disturbance


  • 0

    @zangguodong Not a problem. I am glad you figure it out finally.


  • 0
    S

    select id,date, people from (
    select ,
    (select count(
    ) from stadium where id=aa.id-2 and people>=100) as a,
    (select count() from stadium where id=aa.id-1 and people>=100) as b,
    people>=100 as c,
    (select count(
    ) from stadium where id=aa.id+1 and people>=100) as d,
    (select count(*) from stadium where id=aa.id+2 and people>=100) as e
    from stadium aa) bb
    where c and ((a and b) or (b and d) or (d and e));


  • 0
    S

    select s1.* from
    stadium s1,
    stadium s2,
    stadium s3
    where
    (
    ((s1.id-s2.id=1 and s2.id-s3.id=1) or
    (s3.id-s2.id=1 and s2.id-s1.id=1)or
    (s3.id-s1.id=1 and s1.id-s2.id=1)) and
    (s1.people>=100 and s2.people>=100 and s3.people>=100)
    )
    group by s1.id


  • 0
    1

    However, what if the rows are more than 3?


  • 0
    P

    Select Distinct S1.*
    From Stadium S1, Stadium S2, Stadium S3
    Where S1.People > 99 and S2.People > 99 and S3.People > 99
    And
    (
    (S1.id - S2.id = 1 and S1.id - S3.id = 2 and S2.id - S3.id = 1)
    or
    (S2.id - S1.id = 1 and S2.id - S3.id = 2 and S1.id - S3.id = 1)
    or
    (S3.id - S2.id = 1 and S3.id - S1.id = 2 and S2.id - S1.id = 1)
    )
    Order by S1.ID
    ;


  • 0
    T

    This solution looks confusing, how about the following one
    1.Get all days whose next 3 days are fulfill requirement.
    2.Then join stadium again to get all the 3 records after above ones.

    select distinct d.* from(select a.id from stadium a, stadium b, stadium c where b.id-a.id=1 and c.id- b.id=1 and a.people>=100 and b.people>=100 and c.people>=100) x, stadium d where x.id<=d.id and x.id+2>=d.id;
    

  • 0
    A

    Following solution has improvements:

    1- ID can be random
    2- No self join used
    3- Use of OLAP

    Steps:
    First find list of consecutive dates and rank them together
    Second, find the max rank and go back that number of days from max rank date

    create table tmp1
    as (sel a.*
    ,row_number() over (order by a.date_col reset when b.date_col is null) as rn
    from tmp a 
    left join tmp b
    on a.date_col = b.date_col+1)with data on commit preserve rows;
    

    sel * from
    tmp1
    where date_col between
    (select date_col-rn from tmp1 where rn = (select max(rn) from tmp1))
    and
    (select date_col from tmp1 where rn = (select max(rn) from tmp1))


  • 0
    N

    Do
    t2.id - t3.id =1
    t2.id - t3.id = 2
    really necessary?


  • 0
    S
    This post is deleted!

  • 0
    A

    select distinct s1.id,s1.date,s1.people from stadium s1,stadium s2,stadium s3
    where
    if(
    if((s2.id-s1.id)(s3.id-s2.id)=1,1,0)
    +
    if((s2.id-s1.id)
    (s3.id-s1.id)=-1,1,0)
    =1,1,0
    )
    =1
    and
    s1.people>=100 and s2.people>=100 and s3.people>=100
    order by id;


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.