[SQL/코드카타] 프로그래머스 : 저자 별 카테고리 별 매출액 집계하기 | WITH, JOIN, GROUP BY

문제

https://school.programmers.co.kr/learn/courses/30/lessons/144856

프로그래머스

---

✍️ 문제 요약

• 2022년 1월의 도서 판매 데이터 기준으로
• 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가)을 구하고
• 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력
• 저자 ID 오름차순으로, 같다면, 카테고리 내림차순 정렬

---

✍️ 내가 작성한 코드

WITH j AS (SELECT book_id, sales
           FROM BOOK_SALES 
           WHERE MONTH(sales_date) = '01')

SELECT b.author_id
        , a.author_name
        , b.category
        , (j.sales * b.price) AS TOTAL_SALES
FROM book AS b
JOIN author AS a ON b.author_id=a.author_id
GROUP BY b.author_id, b.category
HAVING (j.sales * b.price)
ORDER BY b.author_id ASC, b.category DESC;

---

🚨 내 쿼리 문제점

📌 예: CTE 사용한 올바른 구조

WITH j AS (
  SELECT book_id, sales
  FROM book_sales
  WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'
)
SELECT 
	b.author_id, 
    a.author_name, 
    b.category, 
    SUM(j.sales * b.price) AS total_sales
FROM book AS b
JOIN j ON b.book_id = j.book_id         -- ⬅️ 반드시 JOIN 🔥🔥🔥🔥🔥
JOIN author AS a ON b.author_id = a.author_id
GROUP BY b.author_id, a.author_name, b.category
ORDER BY b.author_id ASC, b.category DESC;

---

🔍 코드 설명 (내가 시도한 풀이와 다른 풀이)

1. 2022년 1월 판매 데이터 뽑아오기

SELECT *
FROM book_sales
WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'

(팁) MONTH(sales_date) = 01 만 쓰면 인덱스 사용이 안될 수 있으니, 가능하면 BETWEEN 범위 사용 권장

2. BOOK과 join 해서 가격/카테고리 붙여오기

SELECT bs.book_id
  	, bs.sales
        , b.price
        , b.category
        , b.author_id
        , (bs.sales * b.price) AS total
FROM (
	SELECT * 
	FROM book_sales
	WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'
    ) AS bs
JOIN book AS b
  ON bs.book_id=b.book_id

3. AUTHOR 테이블을 join 하여 저자명 붙여오기

SELECT
FROM (
	SELECT *
    FROM book_sales
    WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'
    ) AS bs
JOIN book AS b ON bs.book_id=b.book_id
JOIN author AS a ON b.author_id=a.author_id

4. 집계 적용 -- 핵심 🔥

SELECT
	b.author_id,
    a.author_name,
    b.category,
    SUM(bs.sales * b.price) AS total_sales
FROM (
	SELECT * FROM book_sales
    WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'
    ) AS bs
JOIN book AS b ON bs.book_id=b.book_id
JOIN author AS a ON b.author_id=a.author_id
GROUP BY b.author_id, a.author_name, b.category
ORDER BY b.author_id ASC, b.category DESC;

---

✅ 개선점

1) WITH 구문으로 임시테이블을 만들었다면, 반드시 JOIN 해서 사용해야 참조가 가능함!

# 개선된 코드 설명
FROM book b
JOIN j ON b.book_id = j.book_id

2) GROUP BY , 집계함수를 사용할 경우에는 SELECT 절에 있는 컬럼들을 모두(집계컬럼 제외) GROUP BY 로 묶을 것!

SELECT 
	b.author_id, 
    a.author_name, 
    b.category, 
    SUM(j.sales * b.price) AS total_sales
FROM book b
JOIN j ON b.book_id = j.book_id         
JOIN author a ON b.author_id = a.author_id
GROUP BY b.author_id, a.author_name, b.category     # ⬅️ a.author_name 도 넣어줌!

---

📌 개선된 코드

내가 시도했던 WITH 구문을 사용한 정답 쿼리문

WITH j AS (
    SELECT book_id, sales
    FROM book_sales
    WHERE sales_date BETWEEN '2022-01-01' AND '2022-01-31'
)
SELECT 
    b.author_id,
    a.author_name,
    b.category,
    SUM(j.sales * b.price) AS total_sales
FROM book AS b
JOIN j ON b.book_id = j.book_id
JOIN author AS a ON b.author_id = a.author_id
GROUP BY b.author_id, a.author_name, b.category
ORDER BY b.author_id ASC, b.category DESC;​