[SQL] capital gain loss

리트코드 Curated SQL 70 중 Medium 

 

1) case이용

select stock_name,
    sum(case 
            when operation = "BUY" then -price
            else price
        end
    ) as capital_gain_loss
from Stocks
group by stock_name

 

2) self join 이용

with grouped as (select stock_name, operation, sum(price) as price
                from Stocks
                group by stock_name, operation
                order by stock_name, operation)

select a.stock_name, 
    (a.price - b.price) as capital_gain_loss
from grouped a
join grouped b
on a.stock_name = b.stock_name
and (a.operation = "Sell" and b.operation = "Buy")