DAY 3 / [solvesql] Advent of SQL 2024 3문제 (기증품 비율 계산하기, 온라인 쇼핑몰의 월 별 매출액 집계, 게임 평점 예측하기 1)

기증품 비율 계산하기

SELECT ROUND(COUNT(DISTINCT IF(LOWER(credit) LIKE '%gift%', artwork_id, null))*100/COUNT(DISTINCT artwork_id), 3) AS ratio
FROM artworks

 

요점

1. credit의 값에 대해 먼저 대소문자 통일을 해줘야 깔끔하게 gift가 포함된 경우를 찾을 수 있다.

 

정규표현식을 사용한다면 어떻게 작성할 수 있을까?

-- 방법 1.
SELECT ROUND(COUNT(DISTINCT IF(credit REGEXP '[Gg][Ii][Ff][Tt]', artwork_id, null))*100/COUNT(DISTINCT artwork_id), 3) AS ratio
FROM artworks

-- 방법 2.
SELECT ROUND(COUNT(DISTINCT IF(credit REGEXP '(?i)gift', artwork_id, null))*100/COUNT(DISTINCT artwork_id), 3) AS ratio
FROM artworks

방법 1. [Gg][Ii][Ff][Tt]

방법 2. (?i)gift -> (?i)은 정규표현식에서 대소문자를 구분하지 않도록 설정하는 옵션. (= case-insensitive modifier)

 

 

 

온라인 쇼핑몰의 월 별 매출액 집계

SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS order_month
     , SUM(IF(o.order_id NOT LIKE 'C%', i.price*i.quantity, 0)) AS ordered_amount
     , SUM(IF(o.order_id LIKE 'C%', i.price*i.quantity, 0)) AS canceled_amount
     , SUM(i.price*i.quantity) AS total_amount
FROM orders AS o
LEFT JOIN order_items AS i ON o.order_id = i.order_id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY order_month

 

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

1. 주문 금액을 계산하려면 테이블 조인 필요 (orders, order_items)
2. 월별 주문 금액 집계 

 

 

 

게임 평점 예측하기 1

WITH genre_cal AS (
  -- 1. 장르별 평균 평점 계산하는 테이블 생성
  SELECT genre_id 
       , ROUND(AVG(critic_score), 3) AS avg_c_score
       , CEIL(AVG(critic_count)) AS avg_c_count
       , ROUND(AVG(user_score), 3) AS avg_u_score
       , CEIL(AVG(user_count)) AS avg_u_count
  FROM games
  GROUP BY genre_id 
)

SELECT g.game_id
     , g.name
     , IF(g.critic_score IS NULL, c.avg_c_score, g.critic_score) AS critic_score
     , IF(g.critic_count IS NULL, c.avg_c_count, g.critic_count) AS critic_count
     , IF(g.user_score IS NULL, c.avg_u_score, g.user_score) AS user_score
     , IF(g.user_count IS NULL, c.avg_u_count, g.user_count) AS user_count
FROM games AS g
LEFT JOIN genre_cal AS c ON g.genre_id = c.genre_id  -- 2. 장르별 평균 집계한 테이블과 조인
WHERE year >= 2015 -- 3. 2015년 이후 발매한 게임만 & 평점 정보 누락인 경우만 필터링
  AND (g.critic_score IS NULL OR g.user_score IS NULL)

 

 

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

1. 장르별 평균 평점 계산하는 테이블(genre_cal) 생성

2. game 테이블과 위에서 만든 장르별 평균 집계 테이블(genre_cal)을 조인

3. 필터링 조건:

(1) 2015년 이후 발매한 게임만

(2) 평점 정보 누락된 게임만