Day: September 12, 2019

select 
s.id, 
s.visit_date, 
s.people
from stadium s
join (
    select 
    grp, 
    min(visit_date) as min_date, 
    max(visit_date) as max_date, 
    datediff(max(visit_date), min(visit_date)) as diff
    from(
        select 
        id, 
        visit_date, 
        people,
        row_number() over(order by visit_date) as r,
        id - (row_number() over(order by visit_date)) as grp
        from stadium
        where people >= 100
    ) s
    group by 1
    having datediff(max(visit_date), min(visit_date)) >= 2
) m 
    on s.visit_date >= m.min_date 
    and s.visit_date <= m.max_date
order by 2

select 
fid as id, 
num
from (
    select 
        fid, 
        sum(freq) as num
    from(
        select 
        requester_id as fid, 
        1 as freq
        from request_accepted
        union all
        select 
        accepter_id as fid, 
        1 as fred
        from request_accepted
    ) f
    group by 1
) a
order by num desc
limit 1

select 
seat_id
from(
    select 
    seat_id, 
    free, 
    lead(free,1) over(order by seat_id) as lead_free,
    lag(free,1) over(order by seat_id) as lag_free
    from cinema
) c
where free <> 0
and (
    lead_free = free 
    or lag_free = free
)