문제

폐쇄할 따릉이 정류소 구하기 2
당신은 정류소 정보가 담겨있는 station 테이블과 대여 기록이 담겨 있는 rental_history 테이블을 확인해 폐쇄를 검토할 따릉이 정류소 목록을 추려내는 업무를 받았습니다.
2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력하는 쿼리를 작성해주세요. 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 이미 폐쇄된 정류소이거나 새로이 생긴 정류소 일 수 있으므로 쿼리 결과에 포함되지 않도록 해주세요.
2018년 10월 대비 2019년 10월 정류소 대여/반납 건수 비율 값은 소수점 아래 셋째 자리에서 반올림해 둘째 자리까지 출력되어야 합니다.

풀이 과정

잘못된 방향성

처음에 너무 삽질을 해서 빙빙 돌았던 문제였다
대여/반납 건수를 확인하는 문제니까,
당연히 각각 조건을 WHERE절에 넣어서 count(*)를 계산하고자 했다
하지만 이렇게 하려면 2018-10 반납 건수, 2018-10 대여 건수, 2019-10 반납 건수, 2019-10 대여 건수를 각각 CTE로 구해서,
이렇게 구한 상수 4개를 합산하는 과정을 거치게 된다.

이 과정에서 쿼리도 불필요하게 길어지면서 괜히 복잡해진다
그나마 Oracle이라면 FULL OUTER JOIN이라도 있으니 조금은 나았으려나

대여/반납 건수 합산

대신 사용할 수 있는 건, UNION ALL이다
어차피 이번 문제에서 '대여'와 '반납'을 구분할 필요가 없기에,
'2018-10에 대여한 케이스'와 '2018-10에 반납한 케이스'를 전부 하나의 컬럼으로 잡아버릴 수 있다.
(컬럼 이름이 다르면 별개 컬럼으로 통합이 되므로 컬럼에 별칭을 줘서 하나로 합해야 한다)

SELECT
  strftime ('%Y-%m', rent_at) as "rental_time",
  rent_station_id as "station_id"
FROM
  rental_history
WHERE
  strftime ('%Y-%m', rent_at) in ('2018-10', '2019-10')
UNION ALL
SELECT
  strftime ('%Y-%m', return_at) as "rental_time",
  return_station_id as "station_id"
FROM
  rental_history
WHERE
  strftime ('%Y-%m', return_at) in ('2018-10', '2019-10')

조건에 맞는 경우 count

물론 WHERE절을 적고 count(1)을 할 수도 있겠지만
CASE문과 count를 함께 사용해서 조건에 맞는 경우의 수를 집계할 수도 있다
CASE 결과 조건에 맞는 경우 아무 값을 반환하고,
조건에 맞지 않는 경우의 조건을 명시하지 않으면 null을 반환하게 되므로,
이 상황에서 count()를 해주면 조건에 맞는 경우의 수만 세게 된다
(혹은 SUM(CASE when 조건 then 1 else 0)) 등의 방법도 있다)

SELECT
  station_id,
  count(
    case
      when rental_time = '2019-10' then 1
      else null
    end
  ) as "usage_2019",
  count(
    case
      when rental_time = '2018-10' then 1
      else null
    end
  ) as "usage_2018"
FROM
  station_usage_table
GROUP BY
  station_id
;

최종 코드

WITH
  station_usage_table AS (
    SELECT
      strftime ('%Y-%m', rent_at) as "rental_time",
      rent_station_id as "station_id"
    FROM
      rental_history
    WHERE
      strftime ('%Y-%m', rent_at) in ('2018-10', '2019-10')
    UNION ALL
    SELECT
      strftime ('%Y-%m', return_at) as "rental_time",
      return_station_id as "station_id"
    FROM
      rental_history
    WHERE
      strftime ('%Y-%m', return_at) in ('2018-10', '2019-10')
  ),
  usage_per_station AS (
    SELECT
      station_id,
      count(
        case
          when rental_time = '2019-10' then 1
          else null
        end
      ) as "usage_2019",
      count(
        case
          when rental_time = '2018-10' then 1
          else null
        end
      ) as "usage_2018"
    FROM
      station_usage_table
    GROUP BY
      station_id
  )
SELECT
  us.station_id,
  st.name,
  st.local,
  round(1.00 * usage_2019 / usage_2018, 2) * 100 as "usage_pct"
FROM
  usage_per_station us
  INNER JOIN station st ON us.station_id = st.station_id
WHERE
  usage_2019 <= usage_2018 * 0.5
;