PCSQL 모의고사 for Guest (3)

https://programmers.co.kr/app/with_setting/tests/120812/challenges/databases/134

 

 

lag 써서 바로 이전의 시간이랑만 비교하려 했는데, 잘못되었더라!

 

with total_table as(
    SELECT *, Lag(start_time, 1) 
                OVER(ORDER BY id) AS before_stime
            , lag(end_time)
                over(order by id) as before_etime
    from reservation 
    order by id)


(select id, start_time, end_time
from reservation
limit 1)
union
(select id, start_time, end_time
from total_table
where start_time >= before_etime)
order by start_time

 

 

 

 

이렇게 해야하더라

SELECT r1.id, r1.start_time, r1.end_time
FROM reservation r1
LEFT JOIN reservation r2
ON r1.id > r2.id 
   AND r1.start_time < r2.end_time
   AND r1.end_time > r2.start_time
WHERE r2.id IS NULL
ORDER BY r1.start_time;