SQL 코테 연습 (feat. solvesql - Advent of SQL 2025)

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 <= 4

2026-06-02