[MySQL] leetcode)1393. Capital Gain/Loss

Write an SQL query 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.

The query result format is in the following example.

문제 살펴보기
SQL 조회를 작성하여 각 주식에 대한 자본 손익을 보고합니다.

주식의 자본이득/손실은 주식을 한 번 또는 여러 번 사고 판 후의 총 손익이다.

임의의 순서로 결과 테이블을 반환합니다.

쿼리 결과 형식은 다음 예제와 같습니다.

예제

Input:
Stocks table:
+---------------+-----------+---------------+--------+
| stock_name    | operation | operation_day | price  |
+---------------+-----------+---------------+--------+
| Leetcode      | Buy       | 1             | 1000   |
| Corona Masks  | Buy       | 2             | 10     |
| Leetcode      | Sell      | 5             | 9000   |
| Handbags      | Buy       | 17            | 30000  |
| Corona Masks  | Sell      | 3             | 1010   |
| Corona Masks  | Buy       | 4             | 1000   |
| Corona Masks  | Sell      | 5             | 500    |
| Corona Masks  | Buy       | 6             | 1000   |
| Handbags      | Sell      | 29            | 7000   |
| Corona Masks  | Sell      | 10            | 10000  |
+---------------+-----------+---------------+--------+
Output:
+---------------+-------------------+
| stock_name    | capital_gain_loss |
+---------------+-------------------+
| Corona Masks  | 9500              |
| Leetcode      | 8000              |
| Handbags      | -23000            |
+---------------+-------------------+
Explanation:
Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$.

Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$.

Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$. 
It was bought again at day 4 for 1000$ and was sold at day 5 for 500$. 
At last, it was bought at day 6 for 1000$ and was sold at day 10 for 10000$. 
Capital gain/loss is the sum of capital gains/losses for each ('Buy' --> 'Sell') operation = (1010 - 10) + (500 - 1000) + (10000 - 1000) = 1000 - 500 + 9000 = 9500$.

요약하자면
주식으로 얼마나 벌었는지 손해봤는지를 알려줘야한다
1. Sell - Buy = capital_gain_loss

❗ CASE문 활용
❗ Sell - Buy = capital_gain_loss
=> 그렇기에 operation이 Buy 일 때 price 를 빼주기 위해 -price로

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