[TIL160] solvesql - 폐쇄할 따릉이 정류소 찾기

 2018년 대비 2019년 이용량이 50% 이하로 감소한 대여소 찾기

문제 설명

자전거 대여 이력 데이터에서

각 대여소별

대여 건수 + 반납 건수

를 이용량으로 정의한다.

2018년 10월과 2019년 10월의 이용량을 비교하여

2019년 이용량 ≤ 2018년 이용량의 50%

인 대여소를 조회한다.

추가 조건

2018년 이용량 = 0 제외
2019년 이용량 = 0 제외

출력 컬럼

station_id
name
local
usage_pct

---

내가 작성한 쿼리

WITH return_stats as (
    SELECT return_station_id as station_id,
           SUM(CASE WHEN return_at BETWEEN '2018-10-01' AND '2018-11-01' THEN 1 ELSE 0 END) as '2018_return_cnt',
           SUM(CASE WHEN return_at BETWEEN '2019-10-01' AND '2019-11-01' THEN 1 ELSE 0 END) as '2019_return_cnt'
    FROM rental_history
    WHERE return_at BETWEEN '2018-10-01' AND '2018-11-01'
       OR return_at BETWEEN '2019-10-01' AND '2019-11-01'
    GROUP BY station_id
),

rent_stats as (
    SELECT rent_station_id as station_id,
           SUM(CASE WHEN rent_at BETWEEN '2018-10-01' AND '2018-11-01' THEN 1 ELSE 0 END) as '2018_rent_cnt',
           SUM(CASE WHEN rent_at BETWEEN '2019-10-01' AND '2019-11-01' THEN 1 ELSE 0 END) as '2019_rent_cnt'
    FROM rental_history
    WHERE rent_at BETWEEN '2018-10-01' AND '2018-11-01'
       OR rent_at BETWEEN '2019-10-01' AND '2019-11-01'
    GROUP BY rent_station_id
)

SELECT r.station_id,
       s.name,
       s.local,
       ROUND(
            (2019_rent_cnt + 2019_return_cnt) * 100
            /
            (2018_rent_cnt + 2018_return_cnt),
            2
       ) as usage_pct
FROM rent_stats r
JOIN return_stats re
  ON r.station_id = re.station_id
JOIN station s
  ON s.station_id = r.station_id
GROUP BY r.station_id
HAVING usage_pct <= 50
   AND usage_pct <> 0;

---

정답 쿼리

WITH return_stats AS (

    SELECT
        return_station_id AS station_id,

        SUM(
            CASE
                WHEN return_at >= '2018-10-01'
                 AND return_at < '2018-11-01'
                THEN 1 ELSE 0
            END
        ) AS return_cnt_2018,

        SUM(
            CASE
                WHEN return_at >= '2019-10-01'
                 AND return_at < '2019-11-01'
                THEN 1 ELSE 0
            END
        ) AS return_cnt_2019

    FROM rental_history
    GROUP BY return_station_id
),

rent_stats AS (

    SELECT
        rent_station_id AS station_id,

        SUM(
            CASE
                WHEN rent_at >= '2018-10-01'
                 AND rent_at < '2018-11-01'
                THEN 1 ELSE 0
            END
        ) AS rent_cnt_2018,

        SUM(
            CASE
                WHEN rent_at >= '2019-10-01'
                 AND rent_at < '2019-11-01'
                THEN 1 ELSE 0
            END
        ) AS rent_cnt_2019

    FROM rental_history
    GROUP BY rent_station_id
)

SELECT
    r.station_id,
    s.name,
    s.local,

    ROUND(
        (
            rent_cnt_2019 +
            return_cnt_2019
        ) * 100.0
        /
        NULLIF(
            rent_cnt_2018 +
            return_cnt_2018,
            0
        ),
        2
    ) AS usage_pct

FROM rent_stats r
JOIN return_stats re
    ON r.station_id = re.station_id
JOIN station s
    ON s.station_id = r.station_id

WHERE
    (rent_cnt_2018 + return_cnt_2018) > 0
    AND
    (rent_cnt_2019 + return_cnt_2019) > 0
    AND
    (
        (rent_cnt_2019 + return_cnt_2019) * 100.0
        /
        NULLIF(
            rent_cnt_2018 + return_cnt_2018,
            0
        )
    ) <= 50;

---

 주의점

1. 날짜 필터

내 코드

BETWEEN '2018-10-01'
AND '2018-11-01'

문제점

2018-11-01 00:00:00

까지 포함될 수 있음.

실무 패턴

>= 시작일
AND < 다음달 1일

---

2. 분모 0 방어 없음

내 코드

2019합계 * 100
/
2018합계

문제점

2018합계 = 0

이면

100 / 0

오류 발생.

해결

NULLIF(분모,0)

---

3. HAVING 사용

내 코드

GROUP BY station_id
HAVING usage_pct <= 50

하지만 이미 대여소별 1행 상태.

추가 집계가 없으므로

WHERE

사용이 더 적절.

---

4. 숫자로 시작하는 별칭

내 코드

'2018_rent_cnt'

실무에서는

rent_cnt_2018

형태 권장.

가독성이 훨씬 좋음.

---

핵심 개념

-- 조건부 집계
SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)

-- 비율
현재값 * 100.0 / 기준값

-- 0 나누기 방지
NULLIF(분모,0)

-- 날짜 필터
>= 시작일
AND < 다음기간 시작일

---

한 줄 요약

이 문제는 조건부 집계로 연도별 대여·반납 건수를 계산한 뒤 이용량 비율을 구하는 문제이다.
SUM(CASE WHEN)으로 건수를 집계하고, NULLIF()로 분모 0을 방어하며, 날짜 필터는 >= 시작일 AND < 다음달 1일 패턴을 사용하는 것이 실무적으로 가장 안전하다.