Capital Gain/Loss

Capital Gain/Loss

What is this problem about?

The Capital Gain/Loss interview question is a SQL problem involving stock trades. You are given a Stocks table with columns stock_name, operation ('Buy' or 'Sell'), day, and price. You need to calculate the total capital gain or loss for each stock after all trades. The gain/loss is calculated as the sum of all selling prices minus the sum of all buying prices. This Capital Gain/Loss coding problem tests your ability to perform conditional aggregation.

Why is this asked in interviews?

Robinhood and other fintech firms use this to check if you can handle financial ledger data. It tests your proficiency with SQL GROUP BY and the use of CASE statements or IF functions within a SUM() aggregate. It evaluates how you transform categorical data ('Buy'/'Sell') into numerical weights (+/-).

Algorithmic pattern used

This follows the Database interview pattern of "Group and Conditional Sum."

1. GROUP BY stock_name.
2. For each group, use SUM(CASE WHEN operation = 'Buy' THEN -price ELSE price END).
3. This effectively treats buys as negative cash flow and sells as positive cash flow.

Example explanation

Stock "ABC":

• Buy on Day 1 for 10
• Sell on Day 5 for 15
• Buy on Day 10 for 20
• Sell on Day 15 for 30

1. Calculation: (-10) + 15 + (-20) + 30.
2. Result: 15. The SQL query will aggregate these values across all stocks in the table.

Common mistakes candidates make

• Manual subtraction: Trying to join the "Buy" rows with the "Sell" rows. This is very difficult if there are multiple buy/sell pairs for the same stock.
• Ignoring stock name: Forgetting to group the results, leading to a single total for all stocks combined.
• Sign errors: Accidentally adding buy prices and subtracting sell prices.

Interview preparation tip

When calculating net totals from different types of operations (Credits/Debits, Buys/Sells), always use SUM(CASE ...) to assign positive and negative values. It’s much cleaner and more performant than using multiple joins.