DAY 5 / [solvesql] Advent of SQL 2024 2문제 (게임 개발사의 주력 플랫폼 찾기, 전력 소비량 이동 평균 구하기)

게임 개발사의 주력 플랫폼 찾기

WITH rnk_tab AS (
SELECT *
     , DENSE_RANK() OVER(PARTITION BY developer_id ORDER BY sum_sales DESC) AS rnk
FROM (
      SELECT developer_id
          , platform_id
          , SUM(sales_na+sales_eu+sales_jp+sales_other) AS sum_sales
      FROM games
      GROUP BY developer_id, platform_id
      ) AS tmp
)
SELECT c.name AS developer
     , p.name AS platform
     , r.sum_sales AS sales
FROM (SELECT * FROM rnk_tab WHERE rnk = 1) AS r
INNER JOIN companies AS c ON r.developer_id = c.company_id
INNER JOIN platforms AS p ON r.platform_id = p.platform_id

 

쿼리 작성 전, 먼저 생각해보기

각 게임 개발사의 주력 플랫폼과 해당 플랫폼의 판매량 합계를 집계하는 쿼리

 

1. games 테이블에서 개발사 id, 플랫폼 id 별 판매량 합계
2. 1에서 만든 테이블에 윈도우 함수로 랭크 매기기
3. 2에서 만든 테이블에서 랭크가 1인 경우만 필터링 한 후 companies 테이블, platforms 테이블을 조인하여 결과 출력

 

 

 

전력 소비량 이동 평균 구하기

SELECT DATE_ADD(measured_at, INTERVAL 10 MINUTE) AS end_at
     , ROUND(AVG(zone_quads) OVER(ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_quads
     , ROUND(AVG(zone_smir) OVER(ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2)AS zone_smir
     , ROUND(AVG(zone_boussafou) OVER(ORDER BY measured_at ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 2) AS zone_boussafou
FROM power_consumptions
WHERE measured_at BETWEEN '2017-01-01' AND '2017-01-31 23:50:00'

 

- measured_at은 시작시간이므로 10분을 더하여 end_at을 만들어 준다.

- 윈도우 함수를 이용해 이동 평균을 구할 수 있다.  

 

 

[복습노트] 윈도우 함수 프레임

https://limhm4907.tistory.com/173