[solvesql - day 15] 폐쇄할 따릉이 정류소 찾기 2

https://solvesql.com/problems/find-unnecessary-station-2/

https://solvesql.com/problems/find-unnecessary-station-2/

-- 자전거 정류소(station)별로 2018년 10월과 2019년 10월의 대여 및 반납 데이터를 비교하여 
-- 이용률 변화(usage_pct)가 50% 이하인 정류소 조회

WITH rent_bike AS (
    -- 자전거 대여 데이터 집계
    SELECT 
        rent_station_id, -- 대여 정류소 ID
        SUM(
            CASE strftime('%Y-%m', rent_at)   -- 대여 날짜(rent_at)에서 연월을 추출
                WHEN '2018-10' THEN 1        -- 2018년 10월인 경우 1을 더함
                ELSE 0                       -- 그 외의 경우 0을 더함
            END
        ) AS rent_2018_sum,                  -- 2018년 10월 대여 건수 합계
        SUM(
            CASE strftime('%Y-%m', rent_at)
                WHEN '2019-10' THEN 1        -- 2019년 10월인 경우 1을 더함
                ELSE 0
            END
        ) AS rent_2019_sum                   -- 2019년 10월 대여 건수 합계
    FROM 
        rental_history                        -- 대여 기록 테이블
    GROUP BY 
        rent_station_id                       -- 대여 정류소 ID별로 그룹화
),

return_bike AS (
    -- 자전거 반납 데이터 집계
    SELECT 
        return_station_id, -- 반납 정류소 ID
        SUM(
            CASE strftime('%Y-%m', return_at) -- 반납 날짜(return_at)에서 연월을 추출
                WHEN '2018-10' THEN 1         -- 2018년 10월인 경우 1을 더함
                ELSE 0
            END
        ) AS return_2018_sum,                -- 2018년 10월 반납 건수 합계
        SUM(
            CASE strftime('%Y-%m', return_at)
                WHEN '2019-10' THEN 1         -- 2019년 10월인 경우 1을 더함
                ELSE 0
            END
        ) AS return_2019_sum                 -- 2019년 10월 반납 건수 합계
    FROM 
        rental_history                        -- 대여 기록 테이블
    GROUP BY 
        return_station_id                     -- 반납 정류소 ID별로 그룹화
)

-- 정류소 데이터와 대여/반납 데이터 결합 및 이용률 계산
SELECT 
    station_id,                                -- 정류소 ID
    name,                                     -- 정류소 이름
    local,                                    -- 정류소 지역
    ROUND(
        ((rent_2019_sum + return_2019_sum) * 0.1) / 
        ((rent_2018_sum + return_2018_sum) * 0.1) * 100.0, 
        2
    ) AS usage_pct                            -- 이용률 변화 계산 (2019년 총 대여/반납 수 ÷ 2018년 총 대여/반납 수 * 100)
FROM 
    station AS A                              -- 정류소 정보 테이블
LEFT OUTER JOIN 
    rent_bike AS B                            -- 대여 데이터 테이블
ON 
    A.station_id = B.rent_station_id          -- 정류소 ID와 대여 정류소 ID를 기준으로 결합
LEFT OUTER JOIN 
    return_bike AS C                          -- 반납 데이터 테이블
ON 
    A.station_id = C.return_station_id        -- 정류소 ID와 반납 정류소 ID를 기준으로 결합
WHERE 
    usage_pct != 0                            -- 이용률이 0이 아닌 정류소 필터링
    AND usage_pct <= 50                       -- 이용률 변화가 50% 이하인 정류소 필터링