[SQL] solvesql Advent of SQL 2024 15일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 14일차 - 폐쇄할 따릉이 정류소 찾기 2:https://solvesql.com/problems/find-unnecessary-station-2/

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

 

# 풀이

 

WITH usage_counts AS (
  SELECT
    station_id,
    strftime('%Y-%m', usage_at) AS month,
    COUNT(*) AS usage_count
  FROM (
    SELECT
      rent_station_id AS station_id,
      rent_at AS usage_at
    FROM rental_history
    UNION ALL
    SELECT
      return_station_id AS station_id,
      return_at AS usage_at
    FROM rental_history
  )
  GROUP BY station_id, month
),
usage_2018 AS (
  SELECT
  	station_id,
  	usage_count AS usage_2018
  FROM usage_counts
  WHERE month = '2018-10'
),
usage_2019 AS (
  SELECT
  	station_id,
  	usage_count AS usage_2019
  FROM usage_counts
  WHERE month = '2019-10'
)
SELECT
  s.station_id,
  s.name,
  s.local,
  ROUND((u19.usage_2019 * 100.0) / u18.usage_2018, 2) AS usage_pct
FROM station s
JOIN usage_2018 u18 ON s.station_id = u18.station_id
JOIN usage_2019 u19 ON s.station_id = u19.station_id
WHERE u18.usage_2018 > 0
  AND u19.usage_2019 > 0
  AND usage_pct <= 50;

• usage_counts CTE
  • SELECT
    • station_id: station_id 컬럼을 선택한다.
    • strftime('%Y-%m', usage_at) AS month: usage_at의 년도와 월을 추출하여 month로 저장한다.
    • COUNT(*) AS usage_count: 데이터의 개수를 계산하여 usage_count로 저장한다.
  • FROM
    • SELECT
      
      • rent_station_id AS station_id: rent_station_id 컬럼을 선택하여 station_id로 저장한다.
      • rent_at AS usage_at: rent_at 컬럼을 선택하여 usage_at으로 저장한다.
    •     FROM rental_history: rental_history 테이블에서 데이터를 선택한다.
    •     UNION ALL: 데이터를 결합한다. (중복 데이터 유지)
    •     SELECT
      
      • return_station_id AS station_id: return_station_id 컬럼을 선택하여 station_id로 저장한다.
      • return_at AS usage_at: return_at 컬럼을 선택하여 usage_at으로 저장한다.
    •     FROM rental_history: return_history 테이블에서 데이터를 선택한다.
  • GROUP BY station_id, month: station_id와 month를 기준으로 그룹화한다.
• usage_2018  CTE
• SELECT
  • station_id: station_id 컬럼을 선택한다.
  • usage_count AS usage_2018: usage_count 컬럼을 선택하여 usage_2018로 저장한다.
• FROM usage_counts: usage_counts CTE에서 데이터를 가져온다.
• WHERE month = '2018-10': month의 값이 '2018-10'인 데이터만 선택한다.
• usage_2019 CTE
• SELECT
  • station_id: station_id 컬럼을 선택한다.
  • usage_count AS usage_2019: usage_count 컬럼을 선택하여 usage_2019로 저장한다.
• FROM usage_counts: usage_counts CTE에서 데이터를 가져온다.
• WHERE month = '2019-10': month의 값이 '2019-10'인 데이터만 선택한다.
• SELECT
  
  • s.station_id: station 테이블에서 station_id 컬럼을 선택한다.
  • s.name: station 테이블에서 name 컬럼을 선택한다.
  • s.local: station 테이블에서 local 컬럼을 선택한다.
  • ROUND((u19.usage_2019 * 100.0) / u18.usage_2018, 2) AS usage_pct: usage_2019 CTE의 usage_2019 값에 100.0을 곱한 후, usage_2018 CTE의 usage_2018 값으로 나눈 값을 소수점 둘째 자리까지 반올림하고, 이 값을 usage_pct로 저장한다.
• FROM station s: station 테이블의 별칭을 s로 설정하고, 해당 테이블에서 데이터를 가져온다.
• JOIN usage_2018 u18 ON s.station_id = u18.station_id: usage_2018 CTE의 별칭을 u18로 설정하고, station_id 컬럼을 기준으로 station 테이블과 usage_2018 CTE를 조인한다.
• JOIN usage_2019 u19 ON s.station_id = u19.station_id: usage_2019 CTE의 별칭을 u19로 설정하고, station_id 컬럼을 기준으로 station 테이블과 usage_2019 CTE를 조인한다.
• WHERE u18.usage_2018 > 0: usage_2018 CTE의 usage_2018의 값이 0보다 큰 데이터만 선택한다.
  • AND u19.usage_2019 > 0: usage_2019 CTE의 usage_2019의 값이 0보다 큰 데이터만 선택한다.
  • AND (u19.usage_2019 * 100.0) / u18.usage_2018 <= 50: usage_pct 값이 50 이하인 데이터만 선택한다.
•