Capital Gain/Loss

Capital Gain/Loss

What is this problem about?

The Capital Gain/Loss interview question (alternate variation) focuses on calculating the total profit or loss from stock transactions. You are given a table recording stock names, whether the action was a "Buy" or "Sell," and the price at which the action occurred. The goal is to return a summary table showing each stock and its net capital gain or loss. This Capital Gain/Loss coding problem is a fundamental task for data analysis in financial applications.

Why is this asked in interviews?

Companies like Robinhood ask this to ensure candidates can work with transaction logs. It requires transforming transaction-level data into a summary view. This problem specifically tests your ability to handle non-numerical labels (Buy/Sell) and perform arithmetic across rows that represent different types of financial events.

Algorithmic pattern used

This follows the Database interview pattern.

• Transformation: Convert the 'Buy' action into a negative price and the 'Sell' action into a positive price.
• Aggregation: Sum these transformed prices grouped by the stock name. In SQL, this is typically done using SUM(IF(operation = 'Buy', -price, price)).

Example explanation

If you bought "Google" for 1000 and sold it for 1200, then bought it again for 1100 and sold it for 1300:

• First pair profit: 1200 - 1000 = 200.
• Second pair profit: 1300 - 1100 = 200.
• Total Capital Gain: 400. The conditional sum logic handles this as: (-1000) + 1200 + (-1100) + 1300 = 400.

Common mistakes candidates make

• Join-based approach: Trying to join buy rows with sell rows based on day or rank. This fails if the number of buys and sells isn't perfectly balanced or if they happen on the same day.
• Naming the output column: Forgetting to alias the result as capital_gain_loss as usually required by the problem statement.
• Handling multiple stocks: Failing to include stock_name in the SELECT and GROUP BY clauses.

Interview preparation tip

Be comfortable with the "Pivoting" concept in SQL. While this isn't a full pivot, it uses the same logic of using conditional statements inside aggregate functions to process different categories of data into a single numerical result.