Capital Gain/Loss - Problem
Capital Gain/Loss Calculator
Imagine you're a stock trader who needs to calculate your profit or loss for each stock you've traded. You have a detailed transaction history showing all your
Given a Stocks table with the following structure:
Your task is to write a SQL query that calculates the total capital gain or loss for each stock. The gain/loss is calculated as:
Key Rules:
• Each 'Buy' operation has a corresponding 'Sell' operation later
• Each 'Sell' operation has a corresponding 'Buy' operation before it
• A stock can be bought and sold multiple times
• Return results for each stock showing their total gain/loss
Imagine you're a stock trader who needs to calculate your profit or loss for each stock you've traded. You have a detailed transaction history showing all your
Buy and Sell operations.Given a Stocks table with the following structure:
| Column Name | Type |
|---|---|
| stock_name | varchar |
| operation | enum ('Buy', 'Sell') |
| operation_day | int |
| price | int |
Your task is to write a SQL query that calculates the total capital gain or loss for each stock. The gain/loss is calculated as:
(Total Sell Amount) - (Total Buy Amount)Key Rules:
• Each 'Buy' operation has a corresponding 'Sell' operation later
• Each 'Sell' operation has a corresponding 'Buy' operation before it
• A stock can be bought and sold multiple times
• Return results for each stock showing their total gain/loss
Input & Output
example_1.sql — Basic Stock Trading
$
Input:
Stocks table:
+------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+------------+-----------+---------------+--------+
| Leetcode | Buy | 1 | 1000 |
| Corona | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbag | Buy | 17 | 30000 |
| Corona | Sell | 3 | 1010 |
| Handbag | Sell | 29 | 7000 |
+------------+-----------+---------------+--------+
›
Output:
+------------+-------------------+
| stock_name | capital_gain_loss |
+------------+-------------------+
| Corona | 1000 |
| Handbag | -23000 |
| Leetcode | 8000 |
+------------+-------------------+
💡 Note:
For Corona: Bought at 10, sold at 1010 → gain = 1010 - 10 = 1000. For Handbag: Bought at 30000, sold at 7000 → loss = 7000 - 30000 = -23000. For Leetcode: Bought at 1000, sold at 9000 → gain = 9000 - 1000 = 8000.
example_2.sql — Multiple Transactions
$
Input:
Stocks table:
+------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+------------+-----------+---------------+--------+
| AAPL | Buy | 1 | 150 |
| AAPL | Sell | 2 | 180 |
| AAPL | Buy | 3 | 160 |
| AAPL | Sell | 4 | 200 |
| GOOGL | Buy | 1 | 2500 |
| GOOGL | Sell | 2 | 2600 |
+------------+-----------+---------------+--------+
›
Output:
+------------+-------------------+
| stock_name | capital_gain_loss |
+------------+-------------------+
| AAPL | 70 |
| GOOGL | 100 |
+------------+-------------------+
💡 Note:
For AAPL: Total buys = 150 + 160 = 310, total sells = 180 + 200 = 380 → gain = 380 - 310 = 70. For GOOGL: Bought at 2500, sold at 2600 → gain = 2600 - 2500 = 100.
example_3.sql — Single Stock
$
Input:
Stocks table:
+------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+------------+-----------+---------------+--------+
| Tesla | Buy | 1 | 800 |
| Tesla | Sell | 5 | 750 |
+------------+-----------+---------------+--------+
›
Output:
+------------+-------------------+
| stock_name | capital_gain_loss |
+------------+-------------------+
| Tesla | -50 |
+------------+-------------------+
💡 Note:
For Tesla: Bought at 800, sold at 750 → loss = 750 - 800 = -50. This demonstrates a capital loss scenario.
Constraints
- 1 ≤ Number of rows ≤ 104
- stock_name contains only lowercase English letters
- operation is either 'Buy' or 'Sell'
- 1 ≤ operation_day ≤ 105
- 1 ≤ price ≤ 106
- Each 'Buy' has a corresponding 'Sell' and vice versa
Visualization
Tap to expand
Understanding the Visualization
1
Collect Transactions
Gather all buy and sell transactions for each stock from your trading history
2
Group by Stock
Organize transactions by stock name to calculate per-stock performance
3
Separate Operations
Use conditional logic to sum buy costs separately from sell revenues
4
Calculate Net Gain/Loss
Subtract total buy costs from total sell revenues to get capital gain or loss
Key Takeaway
🎯 Key Insight: Use conditional aggregation (CASE statements) within GROUP BY to calculate buy totals and sell totals simultaneously in a single, efficient query.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code