[programmers] SQL Lv. 4 - 저자 별 카테고리 별 매출액 집계하기

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

프로그래머스

문제 설명
다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 저자 정보(AUTHOR) 테이블입니다.

BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

AUTHOR 테이블은 도서의 저자의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

BOOK_SALES 테이블은 각 도서의 날짜 별 판매량 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

 

문제
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

 

---

풀이

틀린 풀이 🥲

SELECT 
    AUTHOR_ID, AUTHOR_NAME, CATEGORY,
    (PRICE * SUM(SUM_SALES)) AS TOTAL_SALES
FROM 
(
    SELECT 
        B.BOOK_ID, B.PRICE,
        A.AUTHOR_ID, A.AUTHOR_NAME, 
        B.CATEGORY,
        DATE_FORMAT(BS.SALES_DATE, '%Y-%m') AS SALES_YEARMONTH, 
        SUM(SALES) AS SUM_SALES
    FROM BOOK B
    JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
    JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
    GROUP BY SALES_YEARMONTH, B.BOOK_ID
) SUB
WHERE SALES_YEARMONTH = '2022-01'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC

틀린 이유:

합계 계산 위치가 틀렸다.

서브 쿼리 `SUB`에서 `SUM(SALES)`로 `SUM_SALES`를 집계했는데,

최상위 쿼리에서 `SUM(SUM_SALES)`를 다시 수행하면서 `PRICE`와 곱하는 로직이 잘못됐다.

최상위 쿼리에서는 개별 책의 `PRICE`를 알 수 없기 때문이다.

(개별 책(`BOOK_ID`)별로 그룹짓는게 아니라, `AUTHOR_ID`와 `CATEGORY`로 그룹짓기 때문에 `PRICE`가 올바르게 매칭되지 않음)

 

정상 작동하도록 수정:

총 매출액은 개별 책의 판매량과 가격을 곱한 값을 합산해야 한다!

➡️ 서브 쿼리에서 개별 책마다 매출액을 계산하고 이를 최상위 쿼리에서 카테고리별로 합산하도록 함.

 

서브쿼리에서는 `GROUP BY B.BOOK_ID`가 되어있고, `B.BOOK_ID`(개별 책)마다 `PRICE`가 올바르게 매칭되어 있다.

따라서, 매출액을 구할 때 `(SUM(SALES) * B.PRICE) AS SUM_SALES`가 가능하다.

그리고 최상위 쿼리에서는 `SUM(SUM_SALES)`로 `TOTAL_SALES`(총 매출액)를 계산하도록 한다.

 

---

제출

SELECT 
    SUB.AUTHOR_ID, A.AUTHOR_NAME, SUB.CATEGORY,
    SUM(SUB.SUM_SALES) AS TOTAL_SALES
FROM 
(
    SELECT 
        B.BOOK_ID, B.CATEGORY, B.AUTHOR_ID, 
        (SUM(SALES) * B.PRICE) AS SUM_SALES
    FROM BOOK B
    JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
    WHERE BS.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
    GROUP BY 
        B.BOOK_ID, B.CATEGORY, B.AUTHOR_ID
    ORDER BY B.BOOK_ID
) SUB
JOIN AUTHOR A ON SUB.AUTHOR_ID = A.AUTHOR_ID
GROUP BY A.AUTHOR_ID, SUB.CATEGORY
ORDER BY A.AUTHOR_ID, SUB.CATEGORY DESC

 

+ 틀린코드에서 매출액 계산만 수정해도 정답 처리 된다. (틀린 코드에서도 조인 조건은 맞았기 때문에)

하지만 `GROUP BY`와 `SELECT` 컬럼이 일치하지 않기 때문에 위의 제출 코드가 더 옳아?보인다.

(일부 데이터베이스에서는 GROUP BY 절에 포함되지 않은 컬럼은 허용되지 않는다.)

SELECT 
    AUTHOR_ID, AUTHOR_NAME, CATEGORY,
    SUM(SUM_SALES) AS TOTAL_SALES
FROM 
(
    SELECT 
        B.BOOK_ID, 
        A.AUTHOR_ID, A.AUTHOR_NAME, 
        B.CATEGORY,
        DATE_FORMAT(BS.SALES_DATE, '%Y-%m') AS SALES_YEARMONTH, 
        (SUM(SALES) * B.PRICE) AS SUM_SALES
    FROM BOOK B
    JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
    JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
    GROUP BY SALES_YEARMONTH, B.BOOK_ID
) SUB
WHERE SALES_YEARMONTH = '2022-01'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC

 

---

응용

제출 코드 1은 `WHERE`절에서 `BETWEEN`으로 날짜를 직접 비교한다.

제출 코드 2는 위의 설명처럼 `GROUP BY`와 `SELECT` 컬럼이 일치하지 않는다.

 

2022년 1월의 경우 뿐만 아니라 YYYY-mm 기간에 대한 저자와 카테고리별 매출액을 출력하는 쿼리를 제출코드 1과 2의 장점을 합쳐서 작성해보았다.

SELECT 
    A.AUTHOR_ID, A.AUTHOR_NAME, 
    SUB.CATEGORY, SUB.SALES_YEARMONTH,
    SUM(SUM_SALES) AS TOTAL_SALES
FROM 
(
    SELECT 
        B.BOOK_ID, 
        B.CATEGORY,
        B.AUTHOR_ID,
        (SUM(SALES) * B.PRICE) AS SUM_SALES,
        DATE_FORMAT(BS.SALES_DATE, '%Y-%m') AS SALES_YEARMONTH
    FROM BOOK B
    JOIN BOOK_SALES BS ON B.BOOK_ID = BS.BOOK_ID
    GROUP BY SALES_YEARMONTH, B.BOOK_ID, B.CATEGORY, B.AUTHOR_ID
) SUB
JOIN AUTHOR A ON SUB.AUTHOR_ID = A.AUTHOR_ID
-- WHERE SALES_YEARMONTH = '2022-01'
GROUP BY AUTHOR_ID, CATEGORY, SALES_YEARMONTH
ORDER BY SALES_YEARMONTH, AUTHOR_ID, CATEGORY DESC

2022-01 기간 뿐만 아니라, 다른 기간들에 대해서도 저자와 카테고리별 매출액을 출력하는 것을 확인할 수 있다.