후기

case문 문제를 더 풀고 싶어서 찾은 문제이다.
이전에 풀었던 문제랑 답이 비슷해서 좀 아쉽기는 하다.
그치만 case문은 내가 어떻게 접근해서 원하는 바를 얻어낼 것인지에 대해 훈련할 수 있어서 좋은 것 같다.

1393. Capital Gain/Loss

Table: Stocks

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| stock_name | varchar |
| operation | enum |
| operation_day | int |
| price | int |
+---------------+---------+
(stock_name, operation_day) is the primary key (combination of columns with unique values) for this table.
The operation column is an ENUM (category) of type ('Sell', 'Buy')
Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price.
It is guaranteed that each 'Sell' operation for a stock has a corresponding 'Buy' operation in a previous day. It is also guaranteed that each 'Buy' operation for a stock has a corresponding 'Sell' operation in an upcoming day.

Write a solution to report the Capital gain/loss for each stock.

The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.

Return the result table in any order.

풀이

SELECT stock_name,
    SUM(CASE WHEN operation = 'Buy' THEN -price ELSE price END) AS Capital_gain_loss
FROM Stocks
GROUP BY stock_name;