Day 18. 펭귄 날개와 몸무게의 상관 계수https://solvesql.com/problems/correlation-penguin/with t1 as ( select species, flipper_length_mm, body_mass_g , avg(flipper_length_mm) over(PARTITION by species) as avg_f , avg(body_mass_g) over(PARTITION by species) as avg_b from penguins)select species , round(sum((flipper_length_mm - avg_f) * (body_mass_g - avg_b)) / (sqrt(sum(power((flipper_length_mm - avg_f..

Day 17. 멀티 플랫폼 게임 찾기https://solvesql.com/problems/multiplatform-games/-- 같은 이름이어도 년도다 아이디가 다름with t1 as ( select g.game_id, g.name as game_name , year , p.platform_id, p.name as platform_name , case when p.name in ('PS3', 'PS4', 'PSP', 'PSV') then 'Sony' when p.name in ('Wii', 'WiiU', 'DS', '3DS') then 'Nintendo' when p.name in ('X360', 'XONE') then 'Microsoft' ..

Day16. 스테디셀러 작가 찾기https://solvesql.com/problems/find-steadyseller-writers/with t1 as ( select author, year from books where genre = 'Fiction' group by author, year order by author, year), t2 as ( select author , year as p1 , lead(year,4) over(partition by author order by year asc) as p5 from t1)select author, max(p1) as year , count(DISTINCT p1) as depthfrom t2where author in (selec..

Day 15. 폐쇄할 따릉이 정류소 찾기 2https://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 st..

Day 14. 전력 소비량 이동 평균 구하기https://solvesql.com/problems/moving-average-of-power-consumption/WITH T1 AS ( SELECT measured_at AS end_at , ROUND(AVG(zone_quads) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING),2) AS zone_quads , ROUND(AVG(zone_smir) OVER(ORDER BY measured_at ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING),2) AS zone_smir , ROUND(AVG(zone_boussafou) OVER(ORDER B..

Day 13. 게임 개발사의 주력 플랫폼 찾기https://solvesql.com/problems/main-platform-of-game-developers/WITH T1 AS ( SELECT c.name AS developer , p.name AS platform , SUM(g.sales_eu+g.sales_jp+g.sales_na+g.sales_other) AS sales FROM games AS g INNER JOIN platforms AS p ON g.platform_id = p.platform_id INNER JOIN companies AS c ON g.developer_id = c.company_id GROUP BY c.name, p.name ORDER BY c...

Day 12. 3년간 들어온 소장품 집계하기https://solvesql.com/problems/summary-of-artworks-in-3-years/WITH T1 AS ( SELECT YEAR(acquisition_date) AS YEAR , classification FROM artworks WHERE acquisition_date BETWEEN '2014-01-01' AND '2016-12-31'), T2 AS ( SELECT classification FROM artworks GROUP BY classification)SELECT T2.classification , COUNT(CASE WHEN YEAR = 2014 THEN T1.classification END) AS '20..

Day 11. 서울숲 요일별 대기오염도 계산하기https://solvesql.com/problems/weekday-stats-airpollution/WITH T1 AS ( SELECT * , WEEKDAY(measured_at) AS week , CASE WHEN WEEKDAY(measured_at) = 0 THEN '월요일' WHEN WEEKDAY(measured_at) = 1 THEN '화요일' WHEN WEEKDAY(measured_at) = 2 THEN '수요일' WHEN WEEKDAY(measured_at) = 3 THEN '목요일' WHEN WEEKDAY(measured_at) = 4 THEN '금요일' ..