30 Days Of LeetCode Database Day 28 - 1393. Capital Gain/Loss

1Table: Stocks
2 
3+---------------+---------+
4| Column Name   | Type    |
5+---------------+---------+
6| stock_name    | varchar |
7| operation     | enum    |
8| operation_day | int     |
9| price         | int     |
10+---------------+---------+
11(stock_name, operation_day) is the primary key (combination of columns with unique values) for this table.
12The operation column is an ENUM (category) of type ('Sell', 'Buy')
13Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price.
14It 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.
15 
16 
17Write a solution to report the Capital gain/loss for each stock.
18 
19The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.
20 
21Return the result table in any order.

1Example 1:
2 
3Input: 
4Stocks table:
5+---------------+-----------+---------------+--------+
6| stock_name    | operation | operation_day | price  |
7+---------------+-----------+---------------+--------+
8| Leetcode      | Buy       | 1             | 1000   |
9| Corona Masks  | Buy       | 2             | 10     |
10| Leetcode      | Sell      | 5             | 9000   |
11| Handbags      | Buy       | 17            | 30000  |
12| Corona Masks  | Sell      | 3             | 1010   |
13| Corona Masks  | Buy       | 4             | 1000   |
14| Corona Masks  | Sell      | 5             | 500    |
15| Corona Masks  | Buy       | 6             | 1000   |
16| Handbags      | Sell      | 29            | 7000   |
17| Corona Masks  | Sell      | 10            | 10000  |
18+---------------+-----------+---------------+--------+
19Output: 
20+---------------+-------------------+
21| stock_name    | capital_gain_loss |
22+---------------+-------------------+
23| Corona Masks  | 9500              |
24| Leetcode      | 8000              |
25| Handbags      | -23000            |
26+---------------+-------------------+
27Explanation: 
28Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$.
29Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$.
30Corona 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$.
31 

1**SQL**
2 
3WITH buy_record AS (
4    SELECT stock_name, SUM(price) AS buy_price 
5    FROM Stocks WHERE operation = 'Buy'
6    GROUP BY stock_name
7),
8sell_record AS (
9    SELECT stock_name, SUM(price) AS sell_price 
10    FROM Stocks WHERE operation = 'Sell'
11    GROUP BY stock_name
12)
13 
14SELECT 
15    b.stock_name , 
16    (s.sell_price - b.buy_price) AS capital_gain_loss 
17    FROM buy_record b 
18    JOIN sell_record s ON s.stock_name = b.stock_name;