[SQL] 저자 별 카테고리 별 매출액 집계하기

반응형

 

 

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

 

 

정답 쿼리

WITH JAN_SALE AS (
    SELECT *
    FROM BOOK_SALES
    WHERE SALES_DATE LIKE '2022-01%'
),

AUTHOR_CAT_SALE AS (
    SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(J.SALES * B.PRICE) TOTAL_SALES
    FROM AUTHOR A
    JOIN BOOK B
        ON A.AUTHOR_ID = B.AUTHOR_ID
    JOIN JAN_SALE J
        ON B.BOOK_ID = J.BOOK_ID
    GROUP BY A.AUTHOR_NAME, B.CATEGORY
)


SELECT * FROM AUTHOR_CAT_SALE
ORDER BY AUTHOR_ID ASC, CATEGORY DESC