LeetCode 1393 - Capital Gain/Loss

LeetCode Problem 1393

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

The Stocks table records stock trading activity. Each row represents either a Buy or Sell operation for a specific stock on a particular day, along with the transaction price.

The table contains four columns:

Column	Meaning
stock_name	The name of the stock
operation	Either 'Buy' or 'Sell'
operation_day	The day the transaction occurred
price	The transaction price

The problem asks us to compute the total capital gain or loss for every stock.

A capital gain occurs when a stock is sold for more than it was bought for:

gain = sell_price - buy_price

A capital loss occurs when the stock is sold for less than it was bought for:

loss = sell_price - buy_price

Each stock may be bought and sold multiple times. The final answer for a stock is the sum of all gains and losses across all transactions.

The important guarantee in the problem statement is that every Buy has a matching future Sell, and every Sell has a corresponding earlier Buy. This means transactions are always valid and balanced.

The expected output contains:

Column	Meaning
stock_name	The stock name
capital_gain_loss	Total profit or loss

The output order does not matter.

A key observation is that every buy reduces profit, while every sell increases profit. Because all transactions are properly paired, we do not actually need to explicitly match buys and sells one by one. We can simply subtract buy prices and add sell prices.

Some important edge cases include stocks with only one buy/sell pair, stocks with multiple profitable and unprofitable trades, and stocks whose total result is negative. The guarantees in the problem ensure we never encounter unmatched operations.

Approaches

Brute Force Approach

A straightforward solution would explicitly pair each Buy operation with its corresponding Sell operation.

For each stock, we could sort operations by operation_day, then simulate the trading process. Whenever we encounter a Buy, we store its price. When we later encounter a Sell, we compute:

sell_price - buy_price

and add it to the running total.

This approach works because the problem guarantees that operations are balanced and ordered logically over time.

However, this method introduces unnecessary bookkeeping. We need extra logic to track outstanding buys and pair them with sells. While still acceptable for small datasets, it is more complicated than necessary.

Optimal Approach

The key insight is that every transaction contributes positively or negatively depending solely on the operation type.

For a stock:

• A Buy operation decreases total profit
• A Sell operation increases total profit

So instead of pairing transactions explicitly, we can compute:

capital_gain_loss =
SUM(sell prices) - SUM(buy prices)

This dramatically simplifies the solution.

In SQL, this can be implemented using a CASE expression:

SUM(
    CASE
        WHEN operation = 'Sell' THEN price
        ELSE -price
    END
)

We then group by stock_name.

This approach is simpler, cleaner, and more efficient.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n log n)	O(n)	Requires sorting and explicit transaction pairing
Optimal	O(n)	O(1)	Adds sells and subtracts buys directly

Algorithm Walkthrough

1. Read each row from the Stocks table.
2. For every operation, determine whether it contributes positively or negatively to the final profit.

• If the operation is 'Sell', add the price.
• If the operation is 'Buy', subtract the price.

3. Group all rows by stock_name so that each stock accumulates its own total profit or loss.
4. Use an aggregate SUM() function to compute the final value for each stock.
5. Return the resulting table containing:

• stock_name
• capital_gain_loss

Why it works

Every completed transaction consists of one buy and one sell:

profit = sell_price - buy_price

Summing all sells and subtracting all buys produces exactly the same result as computing each transaction individually and then adding them together. Because the problem guarantees valid buy/sell pairing, this invariant always holds.

Python Solution

# Write your MySQL query statement below

SELECT
    stock_name,
    SUM(
        CASE
            WHEN operation = 'Sell' THEN price
            ELSE -price
        END
    ) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;

This solution uses SQL aggregation to compute the final profit for each stock.

The CASE expression converts each transaction into either a positive or negative contribution:

• Sell contributes +price
• Buy contributes -price

The SUM() function accumulates these contributions for every stock independently.

Finally, GROUP BY stock_name ensures each stock receives its own computed capital gain or loss.

The implementation is compact because the mathematical relationship between buys and sells eliminates the need for explicit transaction matching.

Go Solution

// There is no Go implementation for this problem because LeetCode 1393
// is a Database problem that requires writing an SQL query.

Unlike algorithmic LeetCode problems, this database problem is solved entirely with SQL. Therefore, no executable Go function is required.

Worked Examples

Example 1

Input table:

stock_name	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

Processing Leetcode

Operation	Contribution	Running Total
Buy 1000	-1000	-1000
Sell 9000	+9000	8000

Final result:

Leetcode = 8000

Processing Handbags

Operation	Contribution	Running Total
Buy 30000	-30000	-30000
Sell 7000	+7000	-23000

Final result:

Handbags = -23000

Processing Corona Masks

Operation	Contribution	Running Total
Buy 10	-10	-10
Sell 1010	+1010	1000
Buy 1000	-1000	0
Sell 500	+500	500
Buy 1000	-1000	-500
Sell 10000	+10000	9500

Final result:

Corona Masks = 9500

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n)	Each row is processed exactly once
Space	O(1)	Only aggregate values are maintained

The query scans the table a single time and computes grouped aggregates. No additional auxiliary structures proportional to input size are required beyond the database engine's grouping mechanism.

Test Cases

# Example from problem statement
# Leetcode: 9000 - 1000 = 8000
# Handbags: 7000 - 30000 = -23000
# Corona Masks: (1010 - 10) + (500 - 1000) + (10000 - 1000) = 9500

# Single profitable transaction
assert (5000 - 1000) == 4000  # basic gain case

# Single losing transaction
assert (200 - 1000) == -800  # basic loss case

# Multiple mixed transactions
assert (
    (1000 - 100) +
    (500 - 700) +
    (2000 - 1000)
) == 2200  # multiple gains and losses

# Zero net profit
assert (
    (1000 - 1000) +
    (500 - 500)
) == 0  # perfectly balanced trades

# Large values
assert (
    (1_000_000 - 100_000) +
    (5_000_000 - 2_000_000)
) == 3_900_000  # stress large transaction values

# Multiple consecutive trades
assert (
    (300 - 100) +
    (400 - 200) +
    (500 - 300)
) == 600  # repeated trading cycles

Test	Why
Example input	Validates overall correctness
Single profitable transaction	Tests basic gain calculation
Single losing transaction	Tests negative results
Multiple mixed transactions	Ensures aggregation works correctly
Zero net profit	Confirms balanced trades return zero
Large values	Verifies handling of big numbers
Multiple consecutive trades	Tests repeated buy/sell cycles

Edge Cases

Stock With Only One Transaction Pair

A stock may only have one Buy and one Sell. This is the simplest valid scenario, but implementations that overcomplicate transaction tracking can still introduce bugs. The SQL solution handles this naturally because it simply subtracts the buy price and adds the sell price.

Total Capital Loss

Some stocks may produce a negative result overall. For example, buying at 30,000 and selling at 7,000 results in -23,000. Implementations that incorrectly assume profits are always positive would fail here. The aggregation approach correctly preserves negative totals.

Multiple Independent Trading Cycles

A stock can be bought and sold many times. A naive implementation might accidentally overwrite previous profits instead of accumulating them. Because the solution uses SUM(), every transaction contributes independently to the final answer.

Transactions Appearing in Arbitrary Row Order

The rows in the table are not guaranteed to appear sorted by day. A brute-force simulation might require sorting operations first. The optimal solution does not rely on ordering at all, because summing sells and subtracting buys yields the same final result regardless of row order.