[Solvesql - Advent of SQL 2024] 게임 개발사의 주력 플랫폼 찾기

문제는 게임개발사*(companies.name) 별로 가장 매출이 높은 플랫폼을 찾아내는 것  

매출 데이터는 sum (sales_eu + sales_jp + sales_na + sales_other)로 계산된다. 

또 순위 1인 데이터만 최종적으로 출력됨 

WITH ranked_data AS (
  SELECT
    companies.name AS developer,
    platforms.name AS platform,
    SUM(sales_eu + sales_jp + sales_na + sales_other) AS sales,
    RANK() OVER (PARTITION BY companies.name ORDER BY SUM(sales_eu + sales_jp + sales_na + sales_other) DESC) AS ranking
  FROM
    games
    LEFT JOIN companies ON games.developer_id = companies.company_id
    LEFT JOIN platforms ON games.platform_id = platforms.platform_id
  WHERE
    companies.name IS NOT NULL  -- 개발사 정보가 있는 데이터만 선택
  GROUP BY
    platforms.name,
    companies.name
)
SELECT
  developer,
  platform,
  sales
FROM
  ranked_data
WHERE
  ranking = 1;

 

1. WITH ranked_data AS (Common Table Expression, CTE)

WITH ranked_data AS (
  ...
)

 

공통 테이블 표현식(CTE)

사용한 이유는 윈도우함수 rank()로 순위를 매기고 난 후에 1위만 출력해야 하고 또 rank 컬럼은 출력하지 않았어야 됐기 때문에 공통 테이블 표현식으로 데이터를 저장해서 이후 쿼리에서 이를 참조한다 .

 

2. rank() 함수로 순위 매기기

RANK() OVER (PARTITION BY companies.name ORDER BY SUM(...) DESC) AS ranking

 

윈도우 함수

윈도우는 행별로 계산된 결과를 반환하면서도, 원래의 데이터 집합을 유지한다. 

데이터 분석, 순위 매기기, 누적 계산, 비율 계산 등 다양한 작업에 사용된다.

function_name (arguments) OVER (
    [PARTITION BY column_name]
    [ORDER BY column_name ASC|DESC]
    [ROWS or RANGE BETWEEN frame_start AND frame_end]
)

 

---

윈도우 함수의 종류

1. 집계 함수(Aggregate Functions)

• 데이터를 그룹화하지 않고도 각 행에 대해 누적 합계, 평균 등을 계산.
• 예: SUM, AVG, MIN, MAX, COUNT.

SELECT department, employee_id, salary, SUM(salary) 
OVER (PARTITION BY department) AS total_salary_by_dept 
FROM employees;

2. 순위 함수(Ranking Functions)

• 데이터의 순위를 매기는 함수.
  • RANK(): 동일 값은 같은 순위를 부여하며, 다음 순위는 건너뜀.
  • DENSE_RANK(): 동일 값은 같은 순위를 부여하지만, 다음 순위는 건너뛰지 않음.
  • ROW_NUMBER(): 각 행에 고유한 순위를 부여.
  • NTILE(n): 데이터를 n개의 동일한 크기로 나눔.

SELECT department, employee_id, salary, 
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department 
FROM employees;

3. 행 이동 함수(Analytic Functions)

• 이전 또는 다음 행의 값을 참조.
  • LAG(): 이전 행의 값을 가져옴.
  • LEAD(): 다음 행의 값을 가져옴.
  • FIRST_VALUE(): 윈도우에서 첫 번째 값을 반환.
  • LAST_VALUE(): 윈도우에서 마지막 값을 반환.

SELECT employee_id, salary, LAG(salary) OVER (ORDER BY salary) AS previous_salary, LEAD(salary) 
OVER (ORDER BY salary) AS next_salary 
FROM employees;

 

4. 비율 함수(Distribution Functions)

• 데이터 집합 내에서 비율 또는 순위 계산.
  • PERCENT_RANK(): 순위의 백분율을 반환.
  • CUME_DIST(): 누적 분포 계산.

SELECT employee_id, salary, 
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank, 
CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution 
FROM employees;