Realized Gain/Loss by Stock Symbol

Medium

A portfolio accounting service records executed buy and sell operations in a transaction ledger. Table: stocks stock_name: stock symbol or instrument label operation: trade side, either Buy or Sell operation_day: day index of the trade event price: executed trade price for that event Data guarantees: (stock_name, operation_day) is unique. For each stock, every Sell has a corresponding earlier Buy. For each stock, every Buy is eventually matched by a later Sell. Task: Compute the final realized capital gain/loss per stock across all operations. Profit convention: Buy contributes -price Sell contributes +price Output requirements: Return exactly two columns: stock_name capital_gain_loss Result rows may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

stocks:
| stock_name   | operation | operation_day | price |
|--------------|-----------|---------------|-------|
| Alpine AI    | Buy       | 1             | 120   |
| Alpine AI    | Sell      | 4             | 230   |
| Alpine AI    | Buy       | 6             | 200   |
| Alpine AI    | Sell      | 8             | 150   |
| Blue Harbor  | Buy       | 2             | 500   |
| Blue Harbor  | Sell      | 7             | 900   |

Output

[
  {"stock_name":"Alpine AI","capital_gain_loss":60},
  {"stock_name":"Blue Harbor","capital_gain_loss":400}
]

Explanation

Alpine AI has (230-120) + (150-200) = 60. Blue Harbor has one cycle: 900-500 = 400.

Example 2

Input

stocks:
| stock_name    | operation | operation_day | price |
|---------------|-----------|---------------|-------|
| Quant Nova    | Buy       | 3             | 1000  |
| Quant Nova    | Sell      | 5             | 1000  |
| Quant Nova    | Buy       | 7             | 1400  |
| Quant Nova    | Sell      | 9             | 1200  |
| Retail Loop   | Buy       | 1             | 40    |
| Retail Loop   | Sell      | 2             | 20    |

Output

[
  {"stock_name":"Quant Nova","capital_gain_loss":-200},
  {"stock_name":"Retail Loop","capital_gain_loss":-20}
]

Explanation

Quant Nova has 0 on the first cycle and -200 on the second. Retail Loop has a single losing cycle of -20.

Example 3

Input

stocks:
| stock_name     | operation | operation_day | price |
|----------------|-----------|---------------|-------|
| Harbor Metals  | Buy       | 10            | 30000 |
| Harbor Metals  | Sell      | 15            | 41000 |
| Harbor Metals  | Buy       | 20            | 25000 |
| Harbor Metals  | Sell      | 25            | 23000 |
| Urban Energy   | Buy       | 11            | 5000  |
| Urban Energy   | Sell      | 14            | 8000  |
| Urban Energy   | Buy       | 18            | 9000  |
| Urban Energy   | Sell      | 22            | 12000 |

Output

[
  {"stock_name":"Harbor Metals","capital_gain_loss":9000},
  {"stock_name":"Urban Energy","capital_gain_loss":6000}
]

Explanation

Harbor Metals: (41000-30000) + (23000-25000) = 9000. Urban Energy: (8000-5000) + (12000-9000) = 6000.

Constraints

1 <= number of rows in stocks <= 200000
(stock_name, operation_day) is unique
operation is either Buy or Sell
1 <= operation_day <= 1000000000
1 <= price <= 1000000000
For each stock, every Sell has an earlier Buy
For each stock, every Buy has a later Sell
Return exactly two columns: stock_name, capital_gain_loss
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: stock_name, capital_gain_loss

stocks: | stock_name | operation | operation_day | price | |--------------|-----------|---------------|-------| | Alpine AI | Buy | 1 | 120 | | Alpine AI | Sell | 4 | 230 | | Alpine AI | Buy | 6 | 200 | | Alpine AI | Sell | 8 | 150 | | Blue Harbor | Buy | 2 | 500 | | Blue Harbor | Sell | 7 | 900 |

stocks: | stock_name | operation | operation_day | price | |---------------|-----------|---------------|-------| | Quant Nova | Buy | 3 | 1000 | | Quant Nova | Sell | 5 | 1000 | | Quant Nova | Buy | 7 | 1400 | | Quant Nova | Sell | 9 | 1200 | | Retail Loop | Buy | 1 | 40 | | Retail Loop | Sell | 2 | 20 |

stocks: | stock_name | operation | operation_day | price | |----------------|-----------|---------------|-------| | Harbor Metals | Buy | 10 | 30000 | | Harbor Metals | Sell | 15 | 41000 | | Harbor Metals | Buy | 20 | 25000 | | Harbor Metals | Sell | 25 | 23000 | | Urban Energy | Buy | 11 | 5000 | | Urban Energy | Sell | 14 | 8000 | | Urban Energy | Buy | 18 | 9000 | | Urban Energy | Sell | 22 | 12000 |