2024년 12월의 챌린지를 달성했다! 비록 중간에 다른 사람의 코드를 참고해서 아쉬웠지만... 그래도 하루에 하나씩 다 풀어냈다는 것에 의의를 가졌다.   뱃지는 4가지가 있는데 나는 가장 높은 등급의 Advent of SQL 2024를 얻었다  ㅎㅎㅎ 이제 모든 문제 다 풀었으니 다음에 문제 올라오면 또 풀어봐야겠다.

Day 25. 메리 크리스마스 2024https://solvesql.com/problems/merry-christmas-2024/select 'Merry Christmas!'

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() 사용해서 주소에서 시도, 시군구 분리