SQL 문제 풀이 챌린지(15/25) - 폐쇄할 따릉이 정류소 찾기 2

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

"대여/반납 건수" 의 정확한 의미 및 문제의 상세 조건이 불분명 합니다.

 

 

 

15. 폐쇄할 따릉이 정류소 찾기 2

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

WITH usage_counts AS (
    SELECT 
        station_id,
        LEFT(usage_at, 7) AS month,
        COUNT(*) AS usage_count
    FROM (
        SELECT 
            rent_station_id AS station_id,
            rent_at AS usage_at
        FROM rental_history
        WHERE rent_at IS NOT NULL

        UNION ALL

        SELECT 
            return_station_id AS station_id,
            return_at AS usage_at
        FROM rental_history
        WHERE return_at IS NOT NULL
    ) sub
    GROUP BY station_id, month
)
SELECT 
    s.station_id,
    s.name,
    s.local,
    ROUND((u19.usage_count * 100.0) / u18.usage_count, 2) AS usage_pct
FROM station s
    JOIN usage_counts u18 ON s.station_id = u18.station_id AND u18.month = '2018-10'
    JOIN usage_counts u19 ON s.station_id = u19.station_id AND u19.month = '2019-10'
WHERE u18.usage_count > 0 
  AND u19.usage_count > 0
  AND (u19.usage_count * 100.0) / u18.usage_count <= 50
ORDER BY station_id ASC;