Capital Gain/Loss - Problem

Given a table Stocks with columns stock_name, operation, operation_day, and price, calculate the capital gain/loss for each stock.

The capital gain/loss is the total profit or loss after buying and selling the stock one or many times. This is calculated as:

  • Sum of all Sell prices minus sum of all Buy prices for each stock
  • Positive value = capital gain, negative value = capital loss

Note: Each Buy operation has a corresponding Sell operation and vice versa.

Table Schema

Stocks
Column Name Type Description
stock_name PK varchar Name of the stock
operation PK enum Type of operation: 'Buy' or 'Sell'
operation_day PK int Day when the operation occurred
price int Price at which the operation was executed
Primary Key: (stock_name, operation_day)
Note: Each row represents a single buy or sell operation for a stock. All buy operations have corresponding sell operations.

Input & Output

Example 1 — Multiple Stock Transactions
Input Table:
stock_name operation operation_day price
Corona Masks Buy 1 10
Corona Masks Sell 2 12
Handbags Buy 17 30
Handbags Sell 29 7
Output:
stock_name capital_gain_loss
Corona Masks 2
Handbags -23
💡 Note:

Corona Masks: Bought for 10, sold for 12 → gain of 2

Handbags: Bought for 30, sold for 7 → loss of 23 (shown as -23)
Example 2 — Multiple Buy/Sell Operations
Input Table:
stock_name operation operation_day price
Apple Buy 1 100
Apple Buy 2 110
Apple Sell 3 120
Apple Sell 4 130
Output:
stock_name capital_gain_loss
Apple 40
💡 Note:

Apple: Total bought: 100 + 110 = 210, Total sold: 120 + 130 = 250 → gain of 40
Example 3 — Single Stock Break-even
Input Table:
stock_name operation operation_day price
Tesla Buy 10 500
Tesla Sell 15 500
Output:
stock_name capital_gain_loss
Tesla 0
💡 Note:

Tesla: Bought for 500, sold for 500 → no gain or loss (0)

Constraints

  • 1 ≤ stock_name.length ≤ 20
  • operation is either 'Buy' or 'Sell'
  • 1 ≤ operation_day ≤ 1000
  • 1 ≤ price ≤ 1000
  • Each Buy operation has a corresponding Sell operation

Visualization

Tap to expand
Capital Gain/Loss Calculation ProcessStep 1: Input DataStock TransactionsCorona: Buy $10Corona: Sell $12Handbags: Buy $30Handbags: Sell $7Multiple buy/sell operationsper stockGROUP BYstock_nameStep 2: Conditional SumCASE WHEN LogicIF Sell: +priceIF Buy: -priceCorona: +12-10=2Handbags: +7-30=-23Net ResultStep 3: Final OutputCapital Gain/LossCorona Masks+2Handbags-23Positive = GainNegative = LossKey FormulaSUM(CASE WHEN operation = 'Sell' THEN price WHEN operation = 'Buy' THEN -price END)Groups all operations per stock and calculates net gain/loss
Understanding the Visualization
1
Input
Stock transactions with buy/sell operations
2
Group & Aggregate
GROUP BY stock with conditional SUM
3
Output
Net capital gain/loss per stock
Key Takeaway
🎯 Key Insight: Use conditional aggregation to calculate profit/loss in a single GROUP BY query

Related Problems

Asked in
Amazon 28 Microsoft 22 Apple 18 Goldman Sachs 15
23.4K Views
Medium Frequency
~8 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen