Advented of SQL 2024 : 폐쇄할 따릉이 정류소 찾기 2 (DAY 15)

문제 확인

폐쇄할 정류소를 검토하기 위해 2019년 10월 한 달 동안 정류소에서 발생한 대여/반납 건수가 2018년 10월 같은 정류소에서 발생한 대여/반납 건수의 50% 이하인 정류소를 출력하는 쿼리를 작성해주세요.

station_id: 정류소 ID
name: 정류소 이름
local: 정류소 소속 지자체
usage_pct: 2018년 10월 대비 2019년 10월 정류소 대여/반납 건수 비율 (0이상 100이하의 값)

조건

• 소수점 아래 셋째자리에서 반올림해 둘째 자리까지 출력되어야함

• 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소는 이미 폐쇄된 정류소이거나 새로이 생긴 정류소 일 수 있으므로 쿼리 결과에 포함되지 않도록

내가 푼 문제

case 1

대여가 2018년 10월이면서 동시에 반납이 2018년 10월일때 개수를 카운팅하고 2018년 10월 대비 2019년 10월 대여 반납 비율을 usage_pct로 출력함

이때 반납 또는 대여건수가 0이 되면 안되니까 해당 조건 추가해서 진행

with rental_t as (select rent_station_id, round(counting_2019*100.0/ counting_2018,2) as usage_pct
FROM
(
select rent_station_id,
sum(case when strftime('%Y-%m',rent_at) ='2019-10' and strftime('%Y-%m',return_at) ='2019-10' then 1 else 0 end) as counting_2019,
sum(case when strftime('%Y-%m',rent_at) ='2018-10' and strftime('%Y-%m',return_at) ='2018-10' then 1 else 0 end) as counting_2018
from rental_history
group by rent_station_id
having counting_2018>0 and counting_2019>0
) t
)
select station_id, name,local, usage_pct
from rental_t r join station s on r.rent_station_id = s.station_id
where usage_pct <=50
;

결과는?
답이 틀렸다고 나왔다.

case 2

대여가 2018년 10월이지만 반납이 2018년 10월이 아닌 경우도 있을테니까 이 경우를 따로 보고 건수를 counting하기로 함

그래서 기존 case 1에서는 where and 을 써서 반납과 대여 둘다 2018년 10월/2019년 10월을 출력했다면
case 2에서는 반납 + 2018년10월 / 대여 + 2018년10월 이런식으로 따로 출력해서 usage_pct을 구함

with cte_1 as (select rent_station_id,
sum(case  when strftime('%Y-%m',rent_at) ='2018-10' then 1 else 0 end) as rent_2018,
sum(case  when strftime('%Y-%m',return_at) ='2018-10' then 1 else 0 end) as return_2018,
sum(case  when strftime('%Y-%m',rent_at) ='2019-10' then 1 else 0 end) as rent_2019,
sum(case  when strftime('%Y-%m',return_at) ='2019-10' then 1 else 0 end) as return_2019
from 
rental_history
group by rent_station_id ),

cte_2 as (select rent_station_id, round((return_2019+rent_2019)*100.0/(rent_2018+return_2018),2) as usage_pct
from cte_1
where return_2019 <> 0 and rent_2019 <> 0 and return_2018 <> 0 and rent_2018 <> 0 )

select s.station_id, name, local, usage_pct 
from cte_2 c2 join station s on c2.rent_station_id = s.station_id
-- 대여 반납 건수 50% 이하
where usage_pct<=50;

결과는?
똑같이 답이 틀렸다고 나온다

첫번째 컬럼 2번째 레코드에서 수서역이 나와야된다고 한다.(ㅠㅠ)

어디에서 문제인지 모르겠다.
다시 한번 더 풀어봐야지

추가) case 3

총 2개를 간과했다. (ㅠㅠ)

1.

대여가 9월이지만 반납이 10월인경우를 고려해서 진행했지만,
대여한 곳과 반납한 곳이 다를 수 있는 경우도 고려해야한다.

2.

2018년 10월 또는 2019년 한달간 대여/반납건수가 0건이면 출력하면 안된다!
따라서 2018년 반납, 2018년 대여 를 따로 보고 0이상인 값을 출력하는게 아니라
2018년 반납+대여 합쳐서 0 이상인 값을 출력해야한다.

최종 정답 코드

-- 대여와 반납 정류소가 서로 다를 수 있음 & 대여는 9월 반납은 10월일 수도 있음

-- rent_id 기준으로 2018년 10월, 2019년 10월 값 출력하기
with cte_1 as (
select rent_station_id,
sum(case  when strftime('%Y-%m',rent_at) ='2018-10' then 1 else 0 end) as rent_2018,
sum(case  when strftime('%Y-%m',rent_at) ='2019-10' then 1 else 0 end) as rent_2019
from rental_history
group by rent_station_id ),

-- return_id 기준으로 2018년 10월, 2019년 10월 값 출력하기
cte_2 as (
select return_station_id,
sum(case  when strftime('%Y-%m',return_at) ='2018-10' then 1 else 0 end) as return_2018,
sum(case  when strftime('%Y-%m',return_at) ='2019-10' then 1 else 0 end) as return_2019
from rental_history
group by return_station_id ),

-- 2018년 10월 대비 2019년 10월 정류소 대여/반납 건수 비율구하기 
cte_3 as (
select rent_station_id as station_id, round((return_2019+rent_2019)*100.0/(rent_2018+return_2018),2) as usage_pct
from cte_1 c1 join cte_2 c2 on c1.rent_station_id = c2.return_station_id 
-- 2018년 10월 또는 2019년 10월 한 달간 대여/반납 건수가 0건인 정류소 제외
where (return_2019 + rent_2019) >0 and (return_2018 + rent_2018) >0)

-- station과 join & 대여 반납 건수 50% 이하 
select s.station_id, name, local, usage_pct 
from cte_3 c3 join station s on c3.station_id = s.station_id
where usage_pct<=50;