Leetcode - 601. Human Traffic of Stadium

1. 문제

 

 

 

Table: Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date is the column with unique values for this table.
Each row of this table contains the visit date and visit id to the stadium with the number of people during the visit.
As the id increases, the date increases as well.

 

Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

 

https://leetcode.com/problems/human-traffic-of-stadium/

 

 

2. 문제 조건

 

1. the records with three or more rows with consecutive id's

-> LEAD, LAG를 활용하여 앞 뒤의 id를 확인, 연속 여부 판단

2. the number of people is greater than or equal to 100 for each

-> WHERE 절
3. ordered by visit_date in ascending order

-> ORDER BY

 

 

3. 코드 작성

 

-- 첫번째 방법 (비효율적인 풀이)
WITH over_100 AS (
    SELECT id
    FROM stadium
    WHERE people >= 100
), con_sec AS (
    SELECT id
         , LEAD(id, 1) OVER (ORDER BY id) AS next_id
         , LEAD(id, 2) OVER (ORDER BY id) AS next_next_id
    FROM over_100)


SELECT *
FROM stadium
WHERE (id - 1, id, id + 1) IN (
    SELECT *
    FROM con_sec
    WHERE id + 1 = next_id
      AND next_id + 1 = next_next_id
)
   OR (id, id + 1, id + 2) IN (
    SELECT *
    FROM con_sec
    WHERE id + 1 = next_id
      AND next_id + 1 = next_next_id
)
   OR (id - 2, id - 1, id) IN (
    SELECT *
    FROM con_sec
    WHERE id + 1 = next_id
      AND next_id + 1 = next_next_id
)


-- 두번째 방법 (효율적인 풀이)
WITH base AS(
    SELECT *
         , LAG(id, 2) OVER (ORDER BY id) AS pre_pre_id
         , LAG(id, 1) OVER (ORDER BY id) AS pre_id
         , LEAD(id, 1) OVER (ORDER BY id) AS next_id
         , LEAD(id, 2) OVER (ORDER BY id) AS next_next_id
    FROM stadium
    WHERE people >= 100
)

SELECT id
     , visit_date
     , people
FROM base
WHERE (pre_pre_id + 1 = pre_id AND pre_id + 1 = id)
   OR (pre_id + 1 = id AND id + 1 = next_id)
   OR (id + 1 = next_id AND next_id + 1 = next_next_id)
ORDER BY visit_date;

 

 

 

4. 회고

 

• 첫번째 풀이와 두번째 풀이의 논리는 같지만 코드 효율성 면에서 차이가 난다.
  • 첫번째 풀이에서 WHERE절을 통해서 100명 이상인 날을 추리고 나서 연속 여부를 판단했는데 불필요한 과정이었다. SQL문 작동 순서에 따라 WHERE이 SELECT보다 먼저 실행되기 때문이다.
  • 세 가지 경우의 수 (id - 2, id - 1, id / id - 1, id, id + 1/ id, id + 1, id + 2) 를 WHERE절에서 조건으로 넣어주었는데, LEAD와 LAG를 동시에 써서 임시테이블을 만들어놓았다면 쿼리문을 단순화 시킬 수 있었을 것이다.
• WINDOW 함수인 LEAD와 LAG의 기능이 헷갈려서 복습을 하게 되었다.
  • LEAD : 당기기 (다음 것을 가져오기)
  • LAG : 밀기 (이전 것을 가져오기)
  • LEAD는 다음 것을 이끌고 온다, LAG는 지연돼서 이전 것을 먼저 밀어올린다라고 생각해야겠다.