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?


Log in to reply
 

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