SQL 문제 풀이 챌린지(6~10)

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

6. DVD 대여점 우수 고객 찾기

https://solvesql.com/problems/dvdrental-vip/

SELECT
  R.customer_id AS customer_id
FROM rental AS R
  INNER JOIN customer AS C ON R.customer_id = C.customer_id
WHERE active = 1
GROUP BY R.customer_id
HAVING COUNT(*) >= 35

 

 

 

7. 이틀 연속 미세먼지가 나빠진 날
https://solvesql.com/problems/bad-finddust-days-in-a-row/

SELECT
  measured_at AS date_alert
FROM (
    SELECT
        measured_at,
        pm10,
        LAG(pm10, 1) OVER (ORDER BY measured_at) AS prev_pm10,
        LAG(pm10, 2) OVER (ORDER BY measured_at) AS prev2_pm10
    FROM measurements
) AS sub

WHERE pm10 >= 30
AND pm10 > prev_pm10
AND prev_pm10 > prev2_pm10

 

 

 

8. 크리스마스를 기념할 완벽한 와인 찾기 🥂

https://solvesql.com/problems/wines-for-friends/

WITH total_avg AS (
  SELECT
    AVG(density) AS avg_density,
    AVG(residual_sugar) AS avg_residual_sugar
  FROM wines
)
, white_avg AS (
  SELECT
    AVG(pH) AS avg_ph,
    AVG(citric_acid) AS avg_citric_acid
  FROM wines
  WHERE color = 'white'
)

SELECT *
FROM wines
WHERE color = 'white'
  AND quality >= 7
  AND density > (SELECT avg_density FROM total_avg)
  AND residual_sugar > (SELECT avg_residual_sugar FROM total_avg)
  AND pH < (SELECT avg_ph FROM white_avg)
  AND citric_acid > (SELECT avg_citric_acid FROM white_avg);

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

https://solvesql.com/problems/volleyball-players-in-two-consecutive-olympics/

WITH event_record AS (
  SELECT
    year,
    name,
    a.id
  FROM records AS r
      INNER JOIN teams AS t ON r.team_id = t.id
      INNER JOIN events AS e ON r.event_id = e.id              
      INNER JOIN games AS g ON r.game_id = g.id
      INNER JOIN athletes AS a ON r.athlete_id = a.id
  WHERE team = 'KOR'
  AND event = 'Volleyball Women''s Volleyball'
)

, if_sequence AS (
    SELECT 
      year,
      name,
      id,
      LAG(year) OVER (PARTITION BY id ORDER BY year) AS prev_year
    FROM event_record
)

SELECT DISTINCT
  id,
  name
FROM if_sequence
WHERE year = prev_year + 4

10. 올림픽 메달이 있는 배구 선수
https://solvesql.com/problems/volleyball-players-with-medals/

WITH event_record AS (
  SELECT
    a.id,
    name,
    medal
  FROM records AS r
      INNER JOIN teams AS t ON r.team_id = t.id
      INNER JOIN events AS e ON r.event_id = e.id              
      INNER JOIN games AS g ON r.game_id = g.id
      INNER JOIN athletes AS a ON r.athlete_id = a.id
  WHERE team = 'KOR'
  AND event = 'Volleyball Women''s Volleyball'
  AND medal IS NOT NULL
)

SELECT
  id,
  name,
  GROUP_CONCAT(DISTINCT medal ORDER BY medal SEPARATOR ',') AS medals
FROM event_record
GROUP BY id, name