Day 24. 세 명이 서로 친구인 관계 찾기https://solvesql.com/problems/friend-group-of-3/WITH friends_of_3820_1 AS ( SELECT user_a_id friend_id FROM edges WHERE user_b_id = 3820 ), friends_of_3820_2 AS ( SELECT user_b_id friend_id FROM edges WHERE user_a_id = 3820 )SELECT user_a_id, user_b_id, 3820 user_c_idFROM edgesWHERE user_a_id IN (SELECT * FROM friends_of_3820_1) AND u..

Day 23. 유량(Flow)와 저량(Stock)https://solvesql.com/problems/flow-and-stock/select year(acquisition_date) as 'Acquisition year' , count(artwork_id) as 'New acquisitions this year (Flow)' , sum(count(artwork_id)) over (order by year(acquisition_date) rows unbounded preceding) as 'Total collection size (Stock)'from artworkswhere acquisition_date is not nullgroup by year(acquisition_date)❗COUNT..

Day 22. 친구 수 집계하기https://solvesql.com/problems/number-of-friends/with t1 as ( (select user_a_id as user_id , count(*) as num_friends from edges group by user_a_id order by user_a_id) union all (select user_b_id as user_id , count(*) as num_friends from edges group by user_b_id order by user_b_id)), t2 as ( select u.user_id , case when num_friends is not null then num_friends ..

Day 21. 세션 유지 시간을 10분으로 재정의하기https://solvesql.com/problems/redefine-session-2/with t1 as ( select user_pseudo_id, event_name, ga_session_id , lag(event_timestamp_kst,1) over() as b_time , event_timestamp_kst , lead(event_timestamp_kst, 1) over() as n_time from ga where user_pseudo_id = 'a8Xu9GO6TB' order by event_timestamp_kst), t2 as ( select * , timestampdiff(second, b_time,e..

Day 20. 미세먼지 수치의 계절간 차이https://solvesql.com/problems/finedust-seasonal-summary/with t1 as ( select * , case when measured_at between '2022-03-01' and '2022-05-31' then 1 when measured_at between '2022-06-01' and '2022-08-31' then 2 when measured_at between '2022-09-01' and '2022-11-30' then 3 else 4 end as season_n from measurements), t2 as ( select season_n , p..

Day 19. 전국 카페 주소 데이터 정제하기https://solvesql.com/problems/refine-cafe-address/with t1 as ( select * , substring_index(address, ' ',1) as sido , substring_index(substring_index(address, ' ',2),' ',-1) as sigungu from cafes)select sido, sigungu, count(*) as cntfrom t1group by sido, sigunguorder by cnt desc❗시도, 시군구 기준으로 GROUP BY    +) WITH 절 : T1❗SUBSTRING() 사용해서 주소에서 시도, 시군구 분리

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' ..