온라인 쇼핑몰의 월 별 매출액 집계 - solvesql

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

출처 : 온라인 쇼핑몰의 월 별 매출액 집계 SolveSQL

내 답안📕

WITH cal_table AS (
  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS new_order_date
      , CASE
            WHEN SUBSTR(oi.order_id, 1, 1) != 'C' THEN price * quantity
        END AS 'ordered_amount'
      , CASE
            WHEN SUBSTR(oi.order_id, 1, 1) = 'C' THEN price * quantity
        END AS 'canceled_amount'
  FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.order_id
)

SELECT new_order_date AS 'order_month'
     , SUM(ordered_amount) AS 'ordered_amount'
     , SUM(canceled_amount) AS 'canceled_amount'
     , SUM(ordered_amount) + SUM(canceled_amount) AS 'total_amount'
FROM cal_table
GROUP BY order_month
ORDER BY order_month ASC;