SQL 코드카타_09

SQL 코드카타_Human Traffic of Stadium

Human Traffic of Stadium

문제 : Human Traffic of Stadium

# 목적, 목표 : 연속된 id가 3개 이상 있는 행과 각 행의 사람 수가 100명 이상인 레코드를 표시
# 필요한 컬럼 : id, visit_date, people, flag
# 컬럼 조건 : flag = 1
# 사용할 테이블 : Stadium
# join key : 없음
# 테이블 조건 : people >= 100
# 필요한 그룹 : 없음
# 필요한 그룹 조건 : 없음
# 정렬 기준 : visit_date 오름차순
# 주의사항 : 연속된 id가 3개 이상이어야 함

로직

• people >= 100만 추출
• id - 2 ~ id + 2 까지 flag 입력용 컬럼 생성
  • id - 2, id - 1이 id에 있다면, flag = 1
  • id - 1, id + 1이 id에 있다면, flag = 1
  • id + 1, id + 2이 id에 있다면, flag = 1
  • 이외에는 flag = 0
• flag = 1인 경우만 추출하여 visit_date 기준 정렬

코드 뜯어보기

# people >= 100만 추출
with A as (
    SELECT *
    FROM Stadium
    WHERE people >= 100
),

# flag 입력용 컬럼 생성
B as (
    SELECT id, id-2 as id_2, id-1 as id_1, id+1 as id1, id+2 as id2,
    visit_date, people
    FROM A
),

# flag 입력
C as (
    SELECT id,
        case when id_2 in (select id from B) and id_1 in (select id from B) then 1
            when id_1 in (select id from B) and id1 in (select id from B) then 1
            when id1 in (select id from B) and id2 in (select id from B) then 1
        else 0 end as flag,
        visit_date, people
    FROM B
)

# flag = 1인 경우만 추출하여 visit_date 기준 정렬
SELECT id, visit_date, people
FROM C
WHERE flag = 1
ORDER BY visit_date

새로 알게된 점

• 하나의 컬럼 값이 테이블 안의 다른 컬럼 안에 존재하는지 확인하는 방법
  • 컬럼A in (컬럼B) : X
    • "컬럼A가 컬럼B라는 컬럼 값 1개와 비교된다"
  • 컬럼A in (SELECT 컬럼B FROM 테이블) : O
  • IN()에는 값 목록 또는 서브쿼리만 넣을 수 있음