[solvesql/데이터리안] Advent of SQL 2025 🎅

Advent of SQL 2025 완료!

 

25년 12월에 데이터리안에서 진행한 Advent of SQL 🎅

꾸준히 풀다가 몇 문제 남겨놓고 방치하다,, 최근에 마저 풀었다.

 

문제는 공개하면 안 돼서 내가 작성한 쿼리만 올리려고 한다.

혼자 쿼리를 풀다 보면 더 좋은 쿼리가 있을 것 같기 때문에 공유하며 다같이 공부하기 위함!

 

 

혼자 쿼리테스트 풀면서 노션에 하나씩 정리해두었다.

한 번에 푼 문제, 몇 회에 걸쳐 푼 문제, 다른 사람의 쿼리 확인이 필요한 문제 등 정리해두니 확실히 어떤 것을 어려워하는지 알 수 있었다.

 

 

Advent of SQL 2025 쿼리

[12/1] 사랑에 대한 영화 찾기

select title, year, rotten_tomatoes
from movies
where upper(title) like '%LOVE%'
order by rotten_tomatoes desc, year desc
;

 

[12/2] 서울숲에 놀러 가기 좋은 날

select measured_at as good_day
from measurements
where 1=1
  and measured_at between '2022-12-01' and '2022-12-31'
  and pm2_5 <= 9
order by measured_at
;

[12/3] 펭귄의 종과 몸무게 조회하기

select species, body_mass_g
from penguins
where species is not null and body_mass_g is not null
order by body_mass_g desc, species
;

 

[12/4] 12월 우수 고객 찾기

select customer_id
from records
where order_date between '2020-12-01' and '2020-12-31'
group by customer_id
having sum(sales) >= 1000
;

 

[12/5] 스탬프를 찍어드려요

select case when (total_bill) >= 25 then 2
            when (total_bill) >= 15 then 1
          else 0 end as stamp
   , count(1) as count_bill
from tips
group by stamp
order by stamp
;

 

[12/6] DVD 대여점 우수 고객 찾기

select a1.customer_id
from rental a1
inner join customer a2 
  on a1.customer_id = a2.customer_id
  and a2.active = 1
group by a1.customer_id
having count(a1.rental_id) >= 35
;

 

[12/7] 이틀 연속 미세먼지가 나빠진 날

with temp as (
  select measured_at
    , pm10 as day_3
    , lag(pm10) over(order by measured_at) as day_2
    , lag(pm10, 2) over(order by measured_at) as day_1
  from measurements
)
select measured_at as date_alert
from temp 
where 1=1
  and day_1 < day_2
  and day_2 < day_3
  and day_3 >= 30
order by date_alert
;

 

[12/8] 크리스마스를 기념할 완벽한 와인 찾기 🥂

select *
from wines
where 1=1
  and color = 'white'
  and quality >= 7
  and density > (select avg(density) from wines)
  and residual_sugar > (select avg(residual_sugar) from wines)
  and pH < (select avg(pH) from wines where color = 'white')
  and citric_acid > (select avg(citric_acid) from wines where color = 'white')
;

 

[12/9] 두 대회 연속으로 출전한 기록이 있는 배구 선수

select *
from athletes
where id in (
  select distinct athlete_id
  from (
    select a1.athlete_id, a1.game_id, a4.rn, lead(a4.rn) over(partition by a1.athlete_id order by a4.rn) as lead_rn
    from records  a1
    inner join events a2
      on a1.event_id = a2.id
      and a2.event = 'Volleyball Women''s Volleyball' /* 여자 배구 종목 */
    inner join teams a3
      on a1.team_id = a3.id
      and a3.team = 'KOR' /* 대한민국 국가대표팀 */
    inner join (
      select *
        , row_number() over(order by year) as rn
      from games
      where season = 'Summer' /* 하계 올림픽 */
    ) a4
      on a1.game_id = a4.id
  ) tb
  where (lead_rn - rn) = 1  /* 연속으로 출전 */
)
;

 

[12/10] 올림픽 메달이 있는 배구 선수

select a1.athlete_id as id, a4.name, group_concat(a1.medal SEPARATOR ',') as medals
from records  a1
inner join events a2
  on a1.event_id = a2.id
  and a2.event = 'Volleyball Women''s Volleyball' /* 여자 배구 종목 */
inner join teams a3
  on a1.team_id = a3.id
  and a3.team = 'KOR' /* 대한민국 국가대표팀 */
inner join athletes a4
  on a1.athlete_id = a4.id
where a1.medal is not NULL
group by a1.athlete_id, a4.name
;

 

[12/11] 레스토랑의 주중, 주말 매출액 비교하기

select case when day in ('Sun', 'Sat') then 'weekend' else 'weekday' end as week
   , sum(total_bill) as sales
from tips
group by week
order by sales desc
;

 

[12/12] 장르, 연도별 게임 평론가 점수 구하기

select genre
   , max(case when year = 2011 then avg_score end) as score_2011
   , max(case when year = 2012 then avg_score end) as score_2012
   , max(case when year = 2013 then avg_score end) as score_2013
   , max(case when year = 2014 then avg_score end) as score_2014
   , max(case when year = 2015 then avg_score end) as score_2015
from (
  select a2.name as genre, a1.year, round(avg(a1.critic_score), 2) as avg_score
  from games a1
  inner join genres a2
    on a1.genre_id = a2.genre_id
  where a1.year between 2011 and 2015
    and critic_count is not null
  group by a2.name, a1.year
) tb 
group by genre
;

 

[12/13] 매출이 높은 배우 찾기

select a5.first_name, a5.last_name, sum(a1.amount) as total_revenue
from payment a1
inner join rental a2 
  on a1.rental_id = a2.rental_id
  and a1.customer_id = a2.customer_id
inner join inventory a3 
  on a2.inventory_id = a3.inventory_id
inner join film_actor a4 
  on a3.film_id = a4.film_id
inner join actor a5 
   on a4.actor_id = a5.actor_id
group by a5.actor_id, a5.first_name, a5.last_name
order by sum(a1.amount) desc
limit 5
;

 

[12/14] 이달의 작가 후보 찾기

select author
from books
where genre = 'Fiction'
group by author
having count(1) >= 2 
  and avg(user_rating) >= 4.5
  and avg(reviews) >= (select avg(reviews) from books where genre = 'Fiction')
order by author
;

 

[12/15] 한국 감독의 영화 찾기

select a3.name as artist, a1.title
from artworks a1
inner join artworks_artists a2
  on a1.artwork_id = a2.artwork_id
  and a1.classification like 'Film%'
inner join artists a3 
  on a2.artist_id = a3.artist_id
where nationality = 'Korean'
;

 

[12/16] 초기 사용자의 친구 관계 찾기

select user_a_id, user_b_id, id_sum
from (
  select user_a_id, user_b_id
    , (user_a_id+user_b_id) as id_sum
    , ROW_NUMBER() over(order by user_a_id+user_b_id) as rn
    , (count(*) over()) * 0.001 as tot_cnt
  from edges
) tb
where rn < tot_cnt
;

 

[12/17] 신규 유입을 견인하는 카테고리

select a.category, a.sub_category, count(distinct a.order_id) as cnt_orders 
from records a 
inner join customer_stats b 
  on a.customer_id = b.customer_id
where a.order_date = b.first_order_date
group by a.category, a.sub_category
order by count(distinct a.order_id) desc
;

 

[12/18] 인플루언서 마케팅 후보 찾기

with tot_user as (
  select user_a_id, user_b_id
  from edges
  union all
  select user_b_id, user_a_id
  from edges
), friends as (
  select user_a_id, count(distinct user_b_id) as cnt 
  from tot_user
  group by user_a_id
)
select A.user_a_id as user_id
   , B.cnt as friends
   , sum(C.cnt) as friends_of_friends
   , round(sum(C.cnt) / B.cnt, 2) as ratio
from tot_user A 
left join friends B
  on A.user_a_id = B.user_a_id
left join friends C 
  on A.user_b_id = C.user_a_id
where B.cnt >= 100
group by A.user_a_id, B.cnt
order by ratio desc
limit 5
;

 

[12/19] 연도별 순매출 구하기

select year(purchased_at) as year
   , sum(total_price - discount_amount) as net_sales
from transactions
where is_returned = 0   /* 반품되지 않은 거래 */
group by year(purchased_at)
order by year(purchased_at)
;

 

[12/20] 연도별 배송 업체 이용 내역 분석하기

select year(purchased_at) as year
  , sum(if(shipping_method = 'Standard', 1, 0)+if(is_returned = 1, 1, 0)) as standard
  , sum(if(shipping_method = 'Express', 1, 0)) as express
  , sum(if(shipping_method = 'Overnight', 1, 0)) as overnight
from transactions
where is_online_order = 1
group by year(purchased_at)
order by year(purchased_at)
;

 

[12/21] A/B 테스트를 위한 버킷 나누기 1

select distinct customer_id
  , case when customer_id % 10 = 0 then 'A' else 'B' end as bucket
from transactions
order by customer_id
;

 

[12/22] 연속된 이틀간의 누적 주문 계산하기

select order_date, weekday, num_orders_today
   , sum(num_orders_today) over(order by order_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as num_orders_from_yesterday
from (
  select date_format(purchased_at, '%Y-%m-%d') as order_date
    , date_format(purchased_at, '%W') as weekday
    , count(distinct transaction_id) as num_orders_today
  from transactions
  where 1=1
    and purchased_at between '2023-11-01' and '2024-01-01'
    and is_online_order = 1
  group by order_date, weekday
) tb
order by order_date
;

 

[12/23] A/B 테스트를 위한 버킷 나누기 2

select bucket
   , count(distinct customer_id) as user_count
   , round(avg(orders), 2) as avg_orders
   , round(avg(revenue), 2) as avg_revenue
from (
  select if(customer_id % 10 = 0, 'A', 'B') as bucket
    , customer_id
    , count(distinct if(is_returned = 0, transaction_id, null)) as orders
    , sum(if(is_returned = 0, total_price, null)) as revenue
  from transactions 
  group by bucket, customer_id
) tb
group by bucket
;

 

[12/24] 도시별 VIP 고객 찾기

with tb_spent as (
  select city_id 
    , customer_id
    , sum(total_price - discount_amount) as total_spent
  from transactions
  where 1=1
    and is_returned = 0
  group by city_id, customer_id
)
select A.city_id, A.customer_id, A.total_spent
from tb_spent A
inner join (select city_id, max(total_spent) as max_spent from tb_spent group by city_id) B
  on A.city_id = B.city_id
  and A.total_spent = B.max_spent
;

 

[12/25] 산타의 웃음 소리

select 'Ho Ho Ho';

---

 

쿼리 확인해보시고 혹시나 쿼리가 잘못 되었거나 더 좋은 쿼리가 있다면 댓글로 알려주세요!

다들 화이팅 🫡💪💪