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

문제

solvesql.com

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

SQLite

내가 작성한 정답

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

내가 작성한 정답2

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