Capital Gain/Loss

Capital Gain/Loss - Problem

Database Medium

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

Asked in

a Amazon 28 m Microsoft 22 🍎 Apple 18 G Goldman Sachs 15

Use GROUP BY with CASE WHEN conditional aggregation to sum sell prices and subtract buy prices for each stock in a single query.

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.

Common Approaches

✓ Conditional Aggregation with GROUP BY

⏱️ Time: O(n) Space: O(k)

Group all operations by stock name and use conditional aggregation to calculate the difference between total sell prices and total buy prices.

Conditional Aggregation with GROUP BY — Algorithm Steps

Step 1: Group all records by stock_name
Step 2: Use CASE WHEN to conditionally sum sell prices and subtract buy prices
Step 3: Calculate the net capital gain/loss as the difference

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group by stock_name

Conditional Sum

Add sells, subtract buys

Result

Net capital gain/loss

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all records for grouping and aggregation

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique stocks

✓ Linear Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Conditional Aggregation with GROUP BY — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler