[SQL문제풀기] 폐쇄할 따릉이 정류소 찾기 2

문제

solvesql.com

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

SQLite

내가 작성한 정답

with
	  tb18 as ( select station_id, sum(cnt) "2018"
							from (select rent_station_id station_id, count(*) cnt
								    from rental_history
								    where strftime ('%Y%m', rent_at) = '201810'
								    group by rent_station_id
								    union all
								    select return_station_id station_id, count(*) cnt
								    from rental_history
								    where strftime ('%Y%m', return_at) = '201810'
								    group by return_station_id) 
							group by station_id),
							
		  tb19 as ( select station_id, sum(cnt) "2019"
						    from (select rent_station_id station_id, count(*) cnt
						          from rental_history
						          where strftime ('%Y%m', rent_at) = '201910'
						          group by rent_station_id
						          union all 
						          select return_station_id station_id, count(*) cnt
						          from rental_history
						          where strftime ('%Y%m', return_at) = '201910'
						          group by return_station_id)
						    group by station_id)
						    
  select s.station_id,
         s.name,
         s.local,
         round(b."2019"*10000*0.01/a."2018",2) usage_pct
  from tb18 a
  join tb19 b on a.station_id = b.station_id
  join station s on a.station_id = s.station_id
  where a."2018"*0.5 >= b."2019"