[solvesql] SQL 풀어보기 - 온라인 쇼핑몰의 월 별 매출액 집계

✔️ 문제

이 온라인 쇼핑몰의 월 별 매출 규모를 한 눈에 파악할 수 있는 데이터를 만들고 싶습니다. 위 두 테이블의 데이터를 조합해 월 별로 취소 주문을 제외한 주문 금액의 합계, 취소 주문의 금액 합계, 그리고 총 합계를 계산하는 쿼리를 작성해주세요. order_id가 C로 시작하는 주문이 취소 주문입니다. 결과 데이터는 아래 4개 컬럼을 포함해야 하고 order_month 컬럼의 값으로 오름차순 정렬되어 있어야 합니다.

 

✔️ LEVEL 3

 

📌 주요 요구사항

온라인 쇼핑몰의 주문 데이터를 기반으로 다음과 같은 정보를 추출.  이때, order_id가 'C'로 시작하는 경우 취소 주문으로 간주

• 취소되지 않은 주문 금액의 합계
• 취소된 주문 금액의 합계
• 전체 주문 금액의 합계
• 월별(order_month)로 그룹화 및 정렬

WITH order_list AS (
    SELECT
        o.order_id,
        order_date,
        price,
        quantity
    FROM orders o
    LEFT JOIN order_items i ON o.order_id = i.order_id
)
SELECT
    strftime('%Y-%m', order_date) AS order_month,
    SUM(CASE WHEN order_id NOT LIKE 'C%' THEN price * quantity END) AS ordered_amount,
    SUM(CASE WHEN order_id LIKE 'C%' THEN price * quantity END) AS canceled_amount,
    SUM(price * quantity) AS total_amount
FROM order_list
GROUP BY order_month
ORDER BY order_month;

 

✍ 코드 해석

1. CTE(Common Table Expression) 사용
   • WITH order_list AS (...)를 사용하여 orders와 order_items을 조인한 결과를 임시 테이블처럼 활용
2. 월별 그룹화 및 정렬
   • strftime('%Y-%m', order_date)를 사용하여 연-월(YYYY-MM) 형식으로 변환
   • MySQL 환경에서는 date_format( order_date , '%Y-%m') 형식으로 사용
3. 취소 여부에 따른 주문 금액 계산
   • CASE WHEN order_id NOT LIKE 'C%' THEN price * quantity END → 취소되지 않은 주문 금액 합계
   • CASE WHEN order_id LIKE 'C%' THEN price * quantity END → 취소된 주문 금액 합계
   • SUM(price * quantity) → 전체 주문 금액 합계
4. 최종 데이터 정렬
   • ORDER BY order_month를 적용하여 월별 오름차순 정렬

📌 실행 결과

 

• 월별 취소되지 않은 주문 금액(ordered_amount)과 취소된 주문 금액(canceled_amount)이 나뉘어 정리됨
  
• total_amount는 두 값을 합친 전체 주문 금액을 의미함
• 데이터가 연-월 기준으로 정렬되어 있어 트렌드 분석이 용이함