문제

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

풀이

WITH rentCnt AS( -- 대여 정류소
  SELECT
    rent_station_id,
    SUM(CASE WHEN rent_at BETWEEN '2019-10-01' AND '2019-11-01' THEN 1 ELSE 0 END) AS 'ninerent_cnt',
    SUM(CASE WHEN rent_at BETWEEN '2018-10-01' AND '2018-11-01' THEN 1 ELSE 0 END) AS 'eightrent_cnt'
  FROM
    rental_history
  GROUP BY
    rent_station_id
),
returnCnt AS( -- 반납 정류소
  SELECT
    return_station_id,
    SUM(CASE WHEN return_at BETWEEN '2019-10-01' AND '2019-11-01' THEN 1 ELSE 0 END) AS 'ninereturn_cnt',
    SUM(CASE WHEN return_at BETWEEN '2018-10-01' AND '2018-11-01' THEN 1 ELSE 0 END) AS 'eightreturn_cnt'
  FROM
    rental_history
  GROUP BY
    return_station_id
)
SELECT
  s.station_id,
  s.name,
  s.local,
  ROUND(100.0 * (r.ninerent_cnt + t.ninereturn_cnt) / (r.eightrent_cnt + t.eightreturn_cnt),2) AS usage_pct
FROM 
  station s
  LEFT OUTER JOIN rentCnt r
    ON s.station_id = r.rent_station_id
  LEFT OUTER JOIN returnCnt t
    ON s.station_id = t.return_station_id
WHERE 1=1
  AND usage_pct != 0
  AND usage_pct <= 50

오답 노트

BETWEEN의 범위

첫 풀이때는 BETWEEN 2019-10-01 AND 2019-10-31 이런식으로 날짜를 계산하였다.
하지만 이렇게 될 경우 10월 1일 00:00:00부터 10월 31일 00:00:00까지의 범위를 생성하게 되어 10월 31일자의 데이터는 가져오지 못했다.

BETWEEN을 사용해서 날짜 데이터를 구할 경우에는 이런 점을 잘 알고 사용하자.