LeetCode 코딩 테스트 - Capital Gain/Loss(LV.Medium)

 

 

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

 

테이블: Stocks

테이블 구조

컬럼이름 타입

stock_name	varchar
operation	enum
operation_day	int
price	int

• (stock_name, operation_day)는 기본 키(각 행이 유일함)이다.
• operation 컬럼은 ('Sell', 'Buy') 중 하나의 값을 가진다.
• 각 행은 특정 stock_name의 주식이 특정 operation_day에 price 가격으로 거래된 기록을 나타낸다.
• 각 'Sell' 거래는 반드시 이전 날짜에 'Buy' 거래가 존재한다.
• 각 'Buy' 거래는 반드시 이후 날짜에 'Sell' 거래가 존재한다.

---

문제 요구사항

• 각 주식(stock_name)의 총 자본 이익/손실(capital gain/loss)을 계산한다.
• 자본 이익/손실은 해당 주식을 여러 번 사고팔면서 발생한 전체 이익 또는 손실을 의미한다.
• 결과 테이블은 어떤 순서로 반환되어도 상관없다.

---

예제

입력 (Stocks 테이블)

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

출력 (Capital Gain/Loss 결과)

stock_name capital_gain_loss

Corona Masks	9500
Leetcode	8000
Handbags	-23000

---

설명

각 주식별 'Buy' -> 'Sell' 거래에 대해 손익을 계산하고 합산한다.

Leetcode

• Day 1: Buy at 1000
• Day 5: Sell at 9000
• Capital Gain: 9000 - 1000 = 8000

Handbags

• Day 17: Buy at 30000
• Day 29: Sell at 7000
• Capital Loss: 7000 - 30000 = -23000

Corona Masks

1. Day 2: Buy at 10, Day 3: Sell at **1010→ Gain:1010 - 10 = 1000`
2. Day 4: Buy at 1000, Day 5: Sell at **500→ Loss:500 - 1000 = -500`
3. Day 6: Buy at 1000, Day 10: Sell at **10000→ Gain:10000 - 1000 = 9000`
4. Total Capital Gain: 1000 - 500 + 9000 = 9500

 

WITH price_by_op AS (
# 2. case문으로 판 주식은 양수, 산 주식은 음수로 변환한다.
SELECT
    stock_name,
    CASE
        WHEN operation = 'Buy' THEN price_by_op * -1
        WHEN operation = 'Sell' THEN price_by_op 
    END AS price_by_op
FROM (
    # 1. stock_name과 operation 별 group by로, sell과 buy 별 합산 금액을 구한다.
    SELECT
        stock_name,
        operation,
        SUM(price) AS price_by_op
    FROM stocks
    GROUP BY
        stock_name, operation
) AS base
)
# 3. stock_name 별 group by 합산으로 최종 손익 금액을 구한다.
SELECT
    stock_name,
    SUM(price_by_op) AS capital_gain_loss
FROM price_by_op
GROUP BY
    stock_name
    
'''사실 간단하게 SUM(CASE WHEN operation = 'Buy' THEN -price
					      WHEN operation = 'Sell' THEN price END)
                 GROUP BY stock_name
를 사용해서 서브쿼리를 사용하지 않고도 구할 수 있다.'''