[SQL문제풀기] 온라인 쇼핑몰의 월 별 매출액 집계

silver

Sep 18, 2025

Contents

문제 SQLite

문제

solvesql.com

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

SQLite

내가 작성한 정답


select order_month, ordered_amount, canceled_amount, ordered_amount+canceled_amount total_amount 
from (select strftime('%Y-%m',o.order_date) order_month,
              sum(case when o.order_id not like 'C%' then i.price*i.quantity end) ordered_amount,
              sum(case when o.order_id like 'C%' then i.price*i.quantity end) canceled_amount 
        from orders o
        join order_items i on o.order_id = i.order_id
        group by order_month)
order by order_month;

내가 이전에 작성한 정답


select *, ordered_amount+canceled_amount total_amount
from (select strftime('%Y-%m',order_date) order_month ,
            sum(case when quantity>0 then quantity*price end) ordered_amount,
            sum(case when quantity<0 then quantity*price end) canceled_amount
      from orders o
      join order_items i on o.order_id = i.order_id
      group by order_month)
order by order_month

💡

Sign는 주어진 숫자의 부호를 반환하는 함수로 양수일 경우 1 0일 경우 0 음수일 경우 -1를 반환한다.


select *, ordered_amount+canceled_amount total_amount
from (select strftime('%Y-%m',order_date) order_month ,
            sum(case sign(quantity) when 1 then quantity*price end) ordered_amount,
            sum(case sign(quantity) when -1 then quantity*price end) canceled_amount
      from orders o
      join order_items i on o.order_id = i.order_id
      group by order_month)
order by order_month