SQL | SOVESQL 2024 🎄: Day 15. 폐쇄할 따릉이 정류소 찾기 2

 

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

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

with t1 as (
  SELECT rent_station_id, DATE_FORMAT(rent_at, '%Y-%m') as rent_at
    , return_station_id-- , DATE_FORMAT(return_at, '%Y-%m') as return_at
  FROM rental_history
  where DATE_FORMAT(rent_at, '%Y-%m') = '2018-10'
    or DATE_FORMAT(rent_at, '%Y-%m') = '2019-10'
), t2 as (
  select DISTINCT rent_station_id as station_id
    , count(case when rent_at = '2018-10' then rent_station_id end) over(PARTITION by rent_station_id) as cnt_18
    , count(case when rent_at = '2019-10' then rent_station_id end) over(PARTITION by rent_station_id) as cnt_19
  from t1

  union all 

  select DISTINCT return_station_id as station_id
    , count(case when rent_at = '2018-10' then return_station_id end) over(PARTITION by return_station_id) as cnt_18
    , count(case when rent_at = '2019-10' then return_station_id end) over(PARTITION by return_station_id) as cnt_19
  from t1
), t3 as (
  select station_id
    , round(sum(cnt_19) / sum(cnt_18)*100,2) as usage_pct
  from t2
  group by station_id
  having (sum(cnt_18) / 2 ) >= sum(cnt_19) and usage_pct > 0
)

select t3.station_id, s.name, s.local, t3.usage_pct
from t3
  inner join station as s on t3.station_id = s.station_id

❗INNER JOIN으로 계산한 테이블 결합 후 원하는 조건의 칼럼 SELECT 

 

+) WITH 절 : T1

❗WHERE 절에 날짜가 2018-10 OR 2019-10인 것만 필터링

 

 

 

+) WITH 절 : T2

❗DISTINCT 사용해서 고유의 station_id 추출
❗COUNT(), CASE WHEN, 윈도우 함수 사용해서 연월에 따라 개수 세기
❗rent와 return 을 각각 구하고 UNION ALL로 합치기

 

 

 

+) WITH 절 : T3

❗station_id 기준으로 GROUP BY
❗대여/반납건수 비율 구하고 HAVING 절로 2차 필터링