SQL 문제 풀이 챌린지(8/25) - 온라인 쇼핑몰의 월 별 매출액 집계

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

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

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

SELECT 
    LEFT(order_date, 7) AS order_month,
    SUM(CASE WHEN orders.order_id NOT REGEXP 'C' THEN price*quantity ELSE 0 END) AS ordered_amount,
    SUM(CASE WHEN orders.order_id REGEXP 'C' THEN price*quantity ELSE 0 END) AS canceled_amount,

    # total_amount
    SUM(CASE WHEN orders.order_id NOT REGEXP 'C' THEN price*quantity ELSE 0 END) + 
    SUM(CASE WHEN orders.order_id REGEXP 'C' THEN price*quantity ELSE 0 END) AS total_amount

FROM orders
LEFT JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY LEFT(order_date, 7)
ORDER BY LEFT(order_date, 7) ASC