폐쇄할 따릉이 정류소 찾기 2
출처 : 폐쇄할 따릉이 정류소 찾기 2
너무 안풀리길래, 흐르는대로 코드를 적었습니다.
그 결과, 51줄의 코드가 완성됐습니다..
추후에 좀 더 효율적인 코드를 작성해서 들고오겠습니다..
내 답안📕
WITH table_2019 AS (
SELECT rent_station_id AS station_id
, COUNT(*) AS cnt
FROM rental_history
WHERE rent_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
GROUP BY rent_station_id
UNION ALL
SELECT return_station_id AS station_id
, COUNT(*) AS cnt
FROM rental_history
WHERE return_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
GROUP BY return_station_id
) , table_2018 AS (
SELECT rent_station_id AS station_id
, COUNT(*) AS cnt
FROM rental_history
WHERE rent_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
GROUP BY rent_station_id
UNION ALL
SELECT return_station_id AS station_id
, COUNT(*) AS cnt
FROM rental_history
WHERE return_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
GROUP BY return_station_id
), full_table_2019 AS (
SELECT station_id AS station_id_19
, SUM(cnt) AS cnt_19
, 2019 AS year_19
FROM table_2019
GROUP BY station_id
), full_table_2018 AS (
SELECT station_id AS station_id_18
, SUM(cnt) AS cnt_18
, 2018 AS year_18
FROM table_2018
GROUP BY station_id
), pct_table AS (
SELECT *
, (cnt_19 / cnt_18) * 100 AS usage_pct
FROM full_table_2018 AS ft8
LEFT JOIN full_table_2019 AS ft9 ON ft8.station_id_18 = ft9.station_id_19
WHERE NOT (station_id_18 IS NULL OR station_id_19 IS NULL)
AND (cnt_19 / cnt_18) * 100 <= 50
)
SELECT s.station_id AS station_id
, s.name AS name
, s.local AS local
, ROUND(pt.usage_pct, 2) AS usage_pct
FROM pct_table AS pt
INNER JOIN station AS s ON pt.station_id_19 = s.station_id
Hi