solvesql 사이트에 있는 Advent of SQL 2025로 SQL 코테를 연습하고 있다! Advent of SQL 2025 🎅
프로그래머스보다 문제가 재밌고, 좀 더 어렵다. 코딩테스트 준비중이라면, 풀어보면 좋을 것 같다!
최적화된 쿼리는 아니지만, 풀이 방식을 기록하고자 한다
(하루에 2~3문제 목표)
1. 사랑에 대한 영화 찾기
SELECT title
, year
, rotten_tomatoes
FROM movies
WHERE UPPER(title) LIKE '%LOVE%'
ORDER BY rotten_tomatoes DESC, year DESC;2. 서울숲에 놀러 가기 좋은날
SELECT measured_at AS 'good_day'
FROM measurements
WHERE DATE_FORMAT(measured_at, '%Y-%m') = '2022-12'
AND pm2_5 <= 9
ORDER BY good_day ASC;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 ASC;4. 12월 우수 고객 찾기
SELECT r.customer_id
FROM records AS r
WHERE DATE_FORMAT(r.order_date, '%Y-%m') = '2020-12'
GROUP BY r.customer_id
HAVING SUM(r.sales) >= 1000;5. 스탬프를 찍어드려요
SELECT stamp
, COUNT(*) AS 'count_bill'
FROM (SELECT *
, CASE
WHEN total_bill >= 25 THEN 2
WHEN total_bill >= 15 THEN 1
ELSE 0
END AS stamp
FROM tips) AS stamp_with_tips
GROUP BY stamp
ORDER BY stamp ASC;6. DVD 대여점 우수 고객 찾기
SELECT customer_id
FROM customer
WHERE customer_id IN (SELECT customer_id
FROM rental
GROUP BY customer_id
HAVING COUNT(*) >= 35)
AND active = 1;7. 이틀 연속 미세먼지가 나빠진 날 ★★★
SELECT measured_at AS 'date_alert'
FROM (SELECT *
, LAG(pm10, 1) OVER (ORDER BY measured_at) AS 'pm10_lag_one_day'
, LAG(pm10, 2) OVER (ORDER BY measured_at) AS 'pm10_lag_two_day'
FROM measurements) AS measurements_new
WHERE pm10 > pm10_lag_one_day
AND pm10_lag_one_day > pm10_lag_two_day
AND pm10 >= 30
ORDER BY date_alert ASC;윈도우함수 LAG, LEAD를 쓰면 될 것이라 생각했지만, 사용하는 방법을 까먹어서 구글링 찬스를 빌렸다.
※ 해설 강의도 있다
[Day 7] 이틀 연속 미세먼지가 나빠진 날 | 입문반 Week 4 | Advent of SQL 2025 공식 해설 영상
8. 크리스마스를 기념할 완벽한 와인 찾기 🥂
SELECT *
FROM wines
WHERE 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')9. 두 대회 연속으로 출전한 기록이 있는 배구 선수
SELECT DISTINCT athlete_id AS 'id'
, name
FROM (SELECT r.id
, r.athlete_id
, r.age
, a.name
, LEAD(r.age) OVER(PARTITION BY r.athlete_id ORDER BY r.id) AS 'next_age'
-- 연속으로 출전 했는지 안했는지를 판단하기 위해 age를 사용했다. 왜냐하면 올림픽은 4년마다 열리니, 나이도 4살 차이가 날 것이다.
FROM records AS r
INNER JOIN events AS e ON r.event_id = e.id
INNER JOIN teams As t ON r.team_id = t.id
INNER JOIN athletes AS a ON r.athlete_id = a.id
WHERE e.event = 'Volleyball Women''s Volleyball'
AND t.team = 'KOR') AS volleyball_women_table
WHERE next_age - age <= 4
-- 테이블을 보니 저번 올림픽과 다음 올림픽의 나이 차이가 3살 나는 선수도 있었다. 원래는 4살 차이가 정상인 것 같지만, 데이터 상 그리 나왔으니 4 이하로 쿼리를 작성했다.처음에는 WITH절을 활용해서 풀었는데, WITH절을 안써도 풀릴 것 같아서 위 쿼리문으로 작성했다.
-- 초기 쿼리문
WITH new_table AS (
SELECT *
, LEAD(age) OVER(PARTITION BY athlete_id ORDER BY id ASC) AS 'next_age'
FROM records
WHERE athlete_id IN (SELECT r.athlete_id
FROM records AS r
INNER JOIN events AS e ON r.event_id = e.id
INNER JOIN teams As t ON r.team_id = t.id
INNER JOIN athletes AS a ON r.athlete_id = a.id
WHERE e.event = 'Volleyball Women''s Volleyball'
AND t.team = 'KOR'
GROUP BY r.athlete_id
HAVING COUNT(*) >= 2)
)
SELECT DISTINCT n.athlete_id AS id
, a.name
FROM new_table as n
INNER JOIN athletes AS a ON n.athlete_id = a.id
WHERE n.next_age - n.age <= 42026-06-02
10. 올림픽 메달이 있는 배구 선수 ★★★
SELECT DISTINCT r.athlete_id AS 'id'
, a.name
, r.medal AS 'medals'
FROM records AS r
INNER JOIN events AS e ON r.event_id = e.id
INNER JOIN teams AS t ON r.team_id = t.id
INNER JOIN athletes AS a ON r.athlete_id = a.id
WHERE e.event = 'Volleyball Women''s Volleyball'
AND t.team = 'KOR'
AND r.medal IS NOT NULL데이터를 살펴보니 메달을 여러개 가진 배구 선수들이 없어서 해당 코드로 풀었지만, 문제에 맞게 풀기 위해서는 GROUP_CONCAT()을 썼어야 했다!.
올바른.ver
SELECT a.id
, a.name
, GROUP_CONCAT(r.medal SEPARATOR ',') AS medals
FROM records AS r
INNER JOIN events AS e ON r.event_id = e.id
INNER JOIN teams AS t ON r.team_id = t.id
INNER JOIN athletes AS a ON r.athlete_id = a.id
WHERE e.event = 'Volleyball Women''s Volleyball'
AND t.team = 'KOR'
AND r.medal IS NOT NULL
GROUP BY a.id, a.name11. 레스토랑의 주중, 주말 매출액 비교하기
SELECT week
, SUM(total_bill) AS 'sales'
FROM (SELECT *
, IF(day IN ('Sun', 'Sat'), 'weekend', 'weekday') AS 'week'
FROM tips) AS week_tips
GROUP BY week
ORDER BY sales DESC;12. 장르, 연도별 게임 평론가 점수 구하기 ★★★
SELECT ge.name AS genre
, ROUND(AVG(CASE WHEN year = 2011 THEN critic_score END), 2) AS 'score_2011'
, ROUND(AVG(CASE WHEN year = 2012 THEN critic_score END), 2) AS 'score_2012'
, ROUND(AVG(CASE WHEN year = 2013 THEN critic_score END), 2) AS 'score_2013'
, ROUND(AVG(CASE WHEN year = 2014 THEN critic_score END), 2) AS 'score_2014'
, ROUND(AVG(CASE WHEN year = 2015 THEN critic_score END), 2) AS 'score_2015'
FROM games AS ga
INNER JOIN genres AS ge ON ga.genre_id = ge.genre_id
WHERE ga.year BETWEEN 2011 AND 2015
AND ga.critic_score IS NOT NULL
GROUP BY ge.name여러 컬럼을 어떻게 만들어내지? 라는 생각이 들어서 문제를 풀지 못했다..
그래서 유튜브 해설을 참고했다!
13. 매출이 높은 배우 찾기
SELECT a.first_name
, a.last_name
, SUM(p.amount) AS 'total_revenue'
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN film_actor AS fa ON i.film_id = fa.film_id
INNER JOIN actor AS a ON fa.actor_id = a.actor_id
GROUP BY fa.actor_id
ORDER BY total_revenue DESC
LIMIT 5;문제를 푸는데 시간이 좀 걸렸다! 여러 테이블간의 관계를 잘 살피면서 풀어야하는 문제였다.
2026-06-04
14. 이달의 작가 후보 찾기
SELECT author
FROM books
WHERE genre = 'Fiction'
GROUP BY author
HAVING COUNT(*) >= 2
AND AVG(user_rating) >= 4.5
AND AVG(reviews) >= (SELECT AVG(reviews) FROM books WHERE genre = 'Fiction')
ORDER BY author ASC;이 문제는.. 문제를 잘 읽었으면 쉽게 풀리는건데, 어느 한 부분을 캐치를 못해서 푸는데 오래 걸렸다!!
15. 한국 감독의 영화 찾기
SELECT ap.name AS 'artist'
, a.title
FROM artworks AS a
INNER JOIN artworks_artists AS aa ON a.artwork_id = aa.artwork_id
INNER JOIN artists AS ap ON aa.artist_id = ap.artist_id
WHERE a.classification LIKE 'Film%'
AND ap.nationality = 'Korean';2026-06-05
16. 초기 사용자의 친구 관계 찾기
SELECT user_a_id
, user_b_id
, id_sum
FROM (SELECT *
, user_a_id + user_b_id AS 'id_sum'
, RANK() OVER(ORDER BY user_a_id + user_b_id ASC) AS 'rnk'
FROM edges) AS rnk_table
WHERE rnk <= (SELECT COUNT(*) * 0.001 FROM edges);17. 신규 유입을 견인하는 카테고리 ★★★
SELECT r.category
, r.sub_category
, COUNT(DISTINCT r.order_id) AS 'cnt_orders'
FROM records AS r
INNER JOIN customer_stats AS cs ON r.customer_id = cs.customer_id
AND r.order_date = cs.first_order_date
GROUP BY r.category, r.sub_category
ORDER BY cnt_orders DESC;COUNT(*)를 하는 바람에 계속해서 안풀렸다!
COUNT(*)를 하면 OFFICE PAPER를 여러번 주문한 것을 중복해서 COUNT를 해버리기 때문이다.
2026-06-06
18. 인플루언서 마케팅 후보 찾기 ★★★★★
WITH all_connections AS (
SELECT user_a_id AS user_id
, user_b_id AS friend_id
FROM edges
UNION ALL
SELECT user_b_id AS user_id
, user_a_id AS friend_id
FROM edges
), friend_cnts AS (
SELECT user_id
, COUNT(friend_id) AS 'f_cnts'
FROM all_connections
GROUP BY user_id
)
SELECT ac.user_id
, COUNT(ac.friend_id) AS 'friends'
, SUM(fc.f_cnts) AS 'friends_of_friends'
, ROUND(SUM(fc.f_cnts) * 1.0 / COUNT(ac.friend_id), 2) AS 'ratio'
FROM all_connections AS ac
INNER JOIN friend_cnts AS fc ON ac.friend_id = fc.user_id
GROUP BY ac.user_id
HAVING friends >= 100
ORDER BY ratio DESC
LIMIT 5;이건 너무 어려워서 유튜브에 있는 풀이 강의를 보고 풀었다 ㅠ,,
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) ASC;2026-06-07
20. 연도별 배송 업체 이용 내역 분석하기 ★
SELECT YEAR(purchased_at) AS 'year'
, COUNT(CASE WHEN shipping_method = 'Standard' THEN transaction_id END)
+ COUNT(CASE WHEN is_returned = 1 THEN transaction_id END) as 'standard'
, COUNT(CASE WHEN shipping_method = 'Express' THEN transaction_id END) as 'express'
, COUNT(CASE WHEN shipping_method = 'Overnight' THEN transaction_id END) as 'overnight'
FROM transactions
WHERE is_online_order = 1
GROUP BY YEAR(purchased_at)
ORDER BY year ASC;21. A/B 테스트를 위한 버킷 나누기 1
SELECT DISTINCT customer_id
, IF(customer_id % 10 = 0, 'A', 'B') AS 'bucket'
FROM transactions
ORDER BY customer_id ASC;2026-06-08
22. 연속된 이틀간의 누적 주문 계산하기
WITH date_transactions_table AS (
SELECT DATE_FORMAT(purchased_at, '%Y-%m-%d') AS 'purchase_date'
, COUNT(quantity) AS 'cnt'
, LAG(COUNT(quantity)) OVER(ORDER BY DATE_FORMAT(purchased_at, '%Y-%m-%d') ASC)
+ COUNT(quantity) AS 'next_cnt'
FROM transactions
WHERE is_online_order = 1
AND YEAR(purchased_at) = 2023
GROUP BY DATE_FORMAT(purchased_at, '%Y-%m-%d')
ORDER BY purchase_date ASC
)
SELECT purchase_date AS 'order_date'
, CASE
WHEN WEEKDAY(purchase_date) = 0 THEN 'Monday'
WHEN WEEKDAY(purchase_date) = 1 THEN 'Tuesday'
WHEN WEEKDAY(purchase_date) = 2 THEN 'Wednesday'
WHEN WEEKDAY(purchase_date) = 3 THEN 'Thursday'
WHEN WEEKDAY(purchase_date) = 4 THEN 'Friday'
WHEN WEEKDAY(purchase_date) = 5 THEN 'Saturday'
WHEN WEEKDAY(purchase_date) = 6 THEN 'Sunday'
END AS 'weekday'
, cnt AS 'num_orders_today'
, IF(next_cnt IS NULL, cnt, next_cnt) AS 'num_orders_from_yesterday'
FROM date_transactions_table AS dtt;
24. 도시별 VIP 고객 찾기
WITH customer_rnk_table AS (
SELECT city_id
, customer_id
, SUM(total_price - discount_amount) AS total_spent
, RANK() OVER(PARTITION BY city_id ORDER BY SUM(total_price - discount_amount) DESC) AS 'rnk'
FROM transactions
WHERE is_returned = 0
GROUP BY city_id, customer_id
)
SELECT city_id
, customer_id
, total_spent
FROM customer_rnk_table AS crt
WHERE rnk = 1문제에 오프라인이 적혀있어서 오프라인인 고객들만 필터링을 했는데 전체고객 기준이었다. 이것때문에 시간을 좀 잡아먹었다 ㅠ
2026-06-09
23. A/B 테스트를 위한 버킷 나누기 2
SELECT bucket
, COUNT(DISTINCT customer_id) AS user_count
, ROUND(COUNT(transaction_id) / COUNT(DISTINCT customer_id), 2) AS avg_orders
, ROUND(SUM(total_price) / COUNT(DISTINCT customer_id), 2) AS avg_revenue
FROM (SELECT *
, IF(customer_id % 10 = 0, 'A', 'B') AS 'bucket'
FROM transactions
WHERE is_returned = 0) AS ab_test_table
GROUP BY bucket;25. 산타의 웃음 소리
SELECT 'Ho Ho Ho'2026-06-10
여기까지 모든 문제를 풀어봤다! 잘 몰랐던 문제들은 추후에 다시 풀어봐야겠다!
Hi