Capital Gain-Loss

Problem

Table: Stocks

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


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

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


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

Solution

Method 1 – FIFO Queue Simulation in SQL

Intuition

We simulate the buy and sell operations for each stock in order of operation_day. For each stock, we match each ‘Sell’ with the earliest unmatched ‘Buy’ (FIFO), and sum the gain/loss for each pair. This can be done in SQL using window functions and self-joins.

Approach

Assign a running number to each ‘Buy’ and ‘Sell’ operation for each stock, ordered by operation_day.
Pair each ‘Sell’ with the corresponding ‘Buy’ using the running number.
For each pair, compute the gain/loss as (sell price - buy price).
Sum the gain/loss for each stock.
Return the result for all stocks, ordered by stock_name.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


WITH buys 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'
),
sells 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 buys b
JOIN sells s ON b.stock_name = s.stock_name AND b.rn = s.rn
GROUP BY b.stock_name
ORDER BY b.stock_name;

Complexity

⏰ Time complexity: O(n log n) (for window functions and join)
🧺 Space complexity: O(n)

Problem#

Examples#

Example 1#

Solution#

Method 1 – FIFO Queue Simulation in SQL#

Intuition#

Approach#

Code#

Complexity#