SQL | SOVESQL 2024 🎄: Day 08. 온라인 쇼핑몰의 월 별 매출액 집계

Day 08. 온라인 쇼핑몰의 월 별 매출액 집계

https://solvesql.com/problems/shoppingmall-monthly-summary/

WITH T1 AS (
  SELECT oi.order_id
    , DATE_FORMAT(o.order_date, '%Y-%m') AS order_month
    , oi.price * oi.quantity AS amount
  FROM orders AS o 
    INNER JOIN order_items AS oi ON o.order_id = oi.order_id
), n_cancel AS (
  SELECT order_month
    , SUM(amount) AS 'ordered_amount'
  FROM T1
  WHERE order_id NOT LIKE '%C%'
  GROUP BY order_month
), cancel AS  (
  SELECT order_month
    , SUM(amount) AS 'canceled_amount'
  FROM T1
  WHERE order_id LIKE '%C%'
  GROUP BY order_month
)

SELECT nc.order_month
  , nc.ordered_amount
  , c.canceled_amount
  , nc.ordered_amount + c.canceled_amount AS total_amount
FROM n_cancel AS nc 
  INNER JOIN cancel AS c ON nc.order_month = c.order_month
ORDER BY order_month ASC

❗칼럼을 더해서 total_amount 열 생성
❗INNER JOIN을 통해 만들어 둔 테이블 결합

 

 

 

+) WITH 절 : T1

❗DATE_FORMAT() 사용해서 연-월로 포맷 바꾸기
❗INNER JOIN 사용해서 테이블 결합

 

 

 

+) WITH 절 : n_cancel

❗SUM() 집계함수 사용해서 합 구하기
❗WHERE 절에 NOT LIKE 사용해서 C가 포함되지 않게 필터링
❗GROUP BY 절에 연월 사용

 

 

 

+) WITH 절 : cancel

❗SUM() 집계함수 사용해서 합 구하기
❗WHERE 절에 LIKE 사용해서 C가 포함되게 필터링
❗GROUP BY 절에 연월 사용