SQL 문제 풀이 챌린지(11/25) - 서울숲 요일별 대기오염도 계산하기

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

 

 

 

11. 서울숲 요일별 대기오염도 계산하기

https://solvesql.com/problems/weekday-stats-airpollution/

-- FROM절 서브쿼리 삽입, weekday_number_flag 제거 위함
SELECT
  weekday,
  no2,
  o3,
  co,
  so2,
  pm10,
  pm2_5

FROM(
      SELECT
          CASE 
              WHEN DAYOFWEEK(measured_at) = 1 THEN '일요일'
              WHEN DAYOFWEEK(measured_at) = 2 THEN '월요일'
              WHEN DAYOFWEEK(measured_at) = 3 THEN '화요일'
              WHEN DAYOFWEEK(measured_at) = 4 THEN '수요일'
              WHEN DAYOFWEEK(measured_at) = 5 THEN '목요일'
              WHEN DAYOFWEEK(measured_at) = 6 THEN '금요일'
              WHEN DAYOFWEEK(measured_at) = 7 THEN '토요일'
          END AS weekday,
          ROUND(AVG(no2), 4) AS no2,
          ROUND(AVG(o3), 4) AS o3,
          ROUND(AVG(co), 4) AS co,
          ROUND(AVG(so2), 4) AS so2,
          ROUND(AVG(pm10), 4) AS pm10,
          ROUND(AVG(pm2_5), 4) AS pm2_5,
          CASE 
              WHEN DAYOFWEEK(measured_at) = 1 THEN 7 -- 일요일 → 7
              ELSE DAYOFWEEK(measured_at) - 1       -- 월요일~토요일 → 1~6
          END AS weekday_number_flag
          
      FROM measurements
      GROUP BY weekday, weekday_number_flag
      ORDER BY weekday_number_flag
      ) AS sub