[LeetCode][MySQL] 1393. Capital Gain/Loss

Medium 🔗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.

The result format is in the following example.

Example 1:

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$.

✏️문제 풀이

WITH : CTE 정의

WITH Buy_CTE AS (
    SELECT stock_name, price AS buy_price,
           ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Buy'
),
Sell_CTE AS (
    SELECT stock_name, price AS sell_price,
           ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Sell'
)

• Buy_CTE와 Sell_CTE는 각각 주식의 Buy와 Sell 기록만 필터링한 서브쿼리
• ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day)
  • 같은 주식 이름 안에서 (PARTITION BY)
  • 날짜 순서대로 (ORDER BY operation_day)
  • 각 거래에 번호를 매김 (ROW_NUMBER())
• 이 번호를 기준으로 각 Buy와 Sell을 순서대로 매칭

SELECT

SELECT b.stock_name,
       SUM(s.sell_price - b.buy_price) AS capital_gain_loss

• b.stock_name
  • Buy_CTE 테이블의 주식 이름
• capital_gain_loss
  • 각 주식 이름별로 Sell 가격 - Buy 가격을 계산해서 모든 거래의 이익/손해 합계(SUM)

FROM

FROM Buy_CTE b JOIN Sell_CTE s ON b.stock_name = s.stock_name AND b.rn = s.rn

• 같은 주식 이름(stock_name)과 같은 거래 순서(rn)인 Buy와 Sell을 매칭

GROUP BY

GROUP BY b.stock_name

• 주식 이름(b.stock_name )으로 그룹화

💯제출 코드

WITH Buy_CTE AS (
    SELECT stock_name, price AS buy_price,
           ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Buy'
),
Sell_CTE AS (
    SELECT stock_name, price AS sell_price,
           ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Sell'
)
SELECT b.stock_name,
       SUM(s.sell_price - b.buy_price) AS capital_gain_loss
FROM Buy_CTE b JOIN Sell_CTE s ON b.stock_name = s.stock_name AND b.rn = s.rn
GROUP BY b.stock_name