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

2문제 정도 풀어봤는데, 아직은 SQL을 안까먹은건 확실한거 같다.

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

SELECT c.AUTHOR_ID as AUTHOR_ID, 
d.AUTHOR_NAME as AUTHOR_NAME, 
c.CATEGORY as CATEGORY, 
SUM(c.SUM_SALES) as TOTAL_SALES
FROM
    (
    SELECT a.AUTHOR_ID, a.CATEGORY, a.PRICE, a.BOOK_ID, b.SALES, a.PRICE*b.SALES as SUM_SALES
    FROM
        (
        SELECT * 
        FROM BOOK
        ) as a

        INNER JOIN

        (
        SELECT *
        FROM BOOK_SALES
        WHERE '2022-01-01'<= SALES_DATE and SALES_DATE < '2022-02-01'
        ) as b

        ON a.BOOK_ID = b.BOOK_ID
    ) as c
    
    INNER JOIN AUTHOR as d
    
    ON c.AUTHOR_ID = d.AUTHOR_ID
    
GROUP BY c.AUTHOR_ID, d.AUTHOR_NAME, c.CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC

Share this post