626. Exchange Seats

Write an SQL query to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

내 답안

select A.flag as id, A.student
from
(
    select  seat.id
            , student        
            , case 
                when (id%2=1) and (seat.id = (SELECT COUNT(*) FROM seat)) then seat.id
                when seat.id % 2 = 0 then seat.id - 1
		        else seat.id +1
             end as flag
    from    seat
    order by flag
) A

262. Trips and Users

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.

Return the result table in any order.

내 답안

안 어려운데?

select  A.request_at as Day
        , Round(
            sum(
                case when A.status in ('cancelled_by_client','cancelled_by_driver') then 1 
                else 0 end
            ) / count(*) ,2) as 'Cancellation Rate'

from    Trips A

left join   Users B
        on  A.client_id = B.users_id
left join   Users C
        on  A.driver_id = C.users_id
where   1=1
and     A.request_at in("2013-10-01","2013-10-02","2013-10-03")
and     B.role = 'client'
and     C.role = 'driver'
and     B.banned = 'No'
and     C.banned = 'No'

group by    A.request_at
order by    1

601. Human Traffic of Stadium

상당히 더럽게 풀었다. (느낌상)
연속한 3개 이상 숫자이므로
일단 연속한 3개가 나오는(시작하는) index를 얻고
거기서 +1 한 index들 그리고 +2 한 index들을 합치고(union : 중복제거)
order by 1,2,3 해주었다.

내 답안

select  D.*
from    Stadium D
inner join (
    select  distinct A.id -- , next_1.id, next_2.id A.id 이후 자연스럽게 이어진 2개 id는 정답 id 이다.
    -- 
    from    Stadium A
    left join   (
        select  *
        from    Stadium B
        limit   1,100000000 # 1행 제외
    ) next_1
           on   A.id = next_1.id -1
    left join   (
        select  *
        from    Stadium C
        limit   2,100000000 # 2행 제외
    ) next_2
           on   A.id = next_2.id -2
    where       1=1
    and         A.people >= 100
    and         next_1.people >= 100
    and         next_2.people >= 100
) only_id
on D.id = only_id.id 

union 

select  D.* -- D.id + 1, D.visit_date, D.people
from    Stadium D
inner join (
    select  distinct A.id -- , next_1.id, next_2.id A.id 이후 자연스럽게 이어진 2개 id는 정답 id 이다.
    -- 
    from    Stadium A
    left join   (
        select  *
        from    Stadium B
        limit   1,100000000 # 1행 제외
    ) next_1
           on   A.id = next_1.id -1
    left join   (
        select  *
        from    Stadium C
        limit   2,100000000 # 2행 제외
    ) next_2
           on   A.id = next_2.id -2
    where       1=1
    and         A.people >= 100
    and         next_1.people >= 100
    and         next_2.people >= 100
) only_id
on D.id = only_id.id +1

union

select  D.* -- D.id + 2, D.visit_date, D.people
from    Stadium D
inner join (
    select  distinct A.id -- , next_1.id, next_2.id A.id 이후 자연스럽게 이어진 2개 id는 정답 id 이다.
    -- 
    from    Stadium A
    left join   (
        select  *
        from    Stadium B
        limit   1,100000000 # 1행 제외
    ) next_1
           on   A.id = next_1.id -1
    left join   (
        select  *
        from    Stadium C
        limit   2,100000000 # 2행 제외
    ) next_2
           on   A.id = next_2.id -2
    where       1=1
    and         A.people >= 100
    and         next_1.people >= 100
    and         next_2.people >= 100
) only_id
on D.id = only_id.id +2

order by 1,2,3

다른 사람의 (더 좋은) 답

윈도우 펑션을 적극 활용하라.
I've seen pretty many solutions using join of three tables or creating temporary tables with n^3 rows. With my 5-years' working experience on data analysis, I can guarantee you this method will cause you "out of spool space" issue when you deal with a large table in big data field.

I recommend you to learn and master window functions like lead, lag and use them as often as you can in your codes. These functions are very fast, and whenever you find yourself creating duplicate temp tables, you should ask yourself: can I solve this with window functions.

SELECT ID
    , visit_date
    , people
FROM (
    SELECT ID
        , visit_date
        , people
        , LEAD(people, 1) OVER (ORDER BY id) nxt
        , LEAD(people, 2) OVER (ORDER BY id) nxt2
        , LAG(people, 1) OVER (ORDER BY id) pre
        , LAG(people, 2) OVER (ORDER BY id) pre2
    FROM Stadium
) cte 
WHERE (cte.people >= 100 AND cte.nxt >= 100 AND cte.nxt2 >= 100) 
    OR (cte.people >= 100 AND cte.nxt >= 100 AND cte.pre >= 100)  
    OR (cte.people >= 100 AND cte.pre >= 100 AND cte.pre2 >= 100)