2. JOIN + GROUP BY 문제

🟢 연습문제 2-1: 기본 조인

[문제]
고객 테이블과 주문 테이블을 조인하여 
각 고객별 총 주문 금액을 구하시오.

[테이블 구조]
customers: customer_id, customer_name, email, region
orders: order_id, customer_id, order_date, amount

-- 답
SELECT A.CUSTOMER_ID
	, A.CUSTOMER_NAME
	, SUM(AMOUNT) TOTAL_AMOUNT
FROM CUSTOMERS A
INNER JOIN ORDERS B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
GROUP BY A.CUSTOMER_ID
	, A.CUSTOMER_NAME
ORDER BY TOTAL_AMOUNT DESC
;

 

🔴 실전문제 2-1: 카테고리별 상위 고객 분석

[문제]
각 상품 카테고리별로 가장 많이 구매한 고객 TOP 3을 구하시오.
결과에는 카테고리명, 고객명, 해당 카테고리 구매 횟수, 구매 금액이 포함되어야 함.

[테이블 구조]
customers: customer_id, customer_name, email
orders: order_id, customer_id, order_date, amount
order_items: order_item_id, order_id, product_id, quantity, price
products: product_id, product_name, category_id, unit_price
categories: category_id, category_name

- 답
WITH CATE_CUST_TMP AS 
    SELECT E.CATEGORY_ID
        , E.CATEGORY_NAME
        , A.CUSTOMER_ID
        , A.CUSTOMER_NAME
        , COUNT(DISTINCT B.ORDER_ID) ORDER_CNT -- 주문 ID별 상품이 여러 개임
        , SUM(C.quantity*C.price) TOTAL_PRICE
        , ROW_NUMBER() OVER(PARTITION BY E.CATEGORY_ID, E.CATEGORY_NAME
                            ORDER BY SUM(C.quantity*C.price) DESC) RN -- 구매 금액 기준
    FROM CUSTOMERS A
    INNER JOIN ORDERS B 
        ON A.CUSTOMER_ID = B.CUSTOMER_ID
    INNER JOIN ORDER_ITEMS C
        ON B.ORDER_ID = C.ORDER_ID
    INNER JOIN PRODUCTS D
        ON C.product_id = D.product_id
    INNER JOIN categories E
    	ON D.category_id = E.category_id
    GROUP BY D.CATEGORY_ID
        , D.CATEGORY_NAME
        , A.CUSTOMER_ID
        , A.CUSTOMER_NAME
)
SELECT CATEGORY_ID
    , CATEGORY_NAME
    , CUSTOMER_ID
    , CUSTOMER_NAME
    , ORDER_CNT
    , TOTAL_PRICE
FROM CATE_CUST_TMP
WHERE RN <= 3
ORDER BY CATEGORY_ID, RN DESC