{x}
blog image

Capital Gain/Loss

Table: Stocks

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| stock_name    | varchar |
| operation     | enum    |
| operation_day | int     |
| price         | int     |
+---------------+---------+
(stock_name, operation_day) is the primary key (combination of columns with unique values) for this table.
The operation column is an ENUM (category) of type ('Sell', 'Buy')
Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price.
It is guaranteed that each 'Sell' operation for a stock has a corresponding 'Buy' operation in a previous day. It is also guaranteed that each 'Buy' operation for a stock has a corresponding 'Sell' operation in an upcoming day.

 

Write a solution to report the Capital gain/loss for each stock.

The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Stocks table:
+---------------+-----------+---------------+--------+
| stock_name    | operation | 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  |
+---------------+-----------+---------------+--------+
Output: 
+---------------+-------------------+
| stock_name    | capital_gain_loss |
+---------------+-------------------+
| Corona Masks  | 9500              |
| Leetcode      | 8000              |
| Handbags      | -23000            |
+---------------+-------------------+
Explanation: 
Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$.
Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$.
Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$. It was bought again at day 4 for 1000$ and was sold at day 5 for 500$. At last, it was bought at day 6 for 1000$ and was sold at day 10 for 10000$. Capital gain/loss is the sum of capital gains/losses for each ('Buy' --> 'Sell') operation = (1010 - 10) + (500 - 1000) + (10000 - 1000) = 1000 - 500 + 9000 = 9500$.

Solution Explanation for LeetCode 1393: Capital Gain/Loss

This problem requires calculating the net capital gain or loss for each stock based on a series of buy and sell transactions. The solution leverages SQL's aggregate functions and conditional logic to efficiently compute this.

Approach

The core idea is to group the transactions by stock_name and then sum the price changes, treating buy operations as negative values and sell operations as positive values. This way, summing these price changes directly gives the net capital gain/loss.

SQL Solution (MySQL)

SELECT
    stock_name,
    SUM(IF(operation = 'Buy', -price, price)) AS capital_gain_loss
FROM Stocks
GROUP BY 1;

Explanation:

  1. SELECT stock_name, ...: This selects the stock name for the output.
  2. SUM(IF(operation = 'Buy', -price, price)): This is the heart of the calculation.
    • IF(operation = 'Buy', -price, price): This conditional statement checks the operation column. If it's 'Buy', it negates the price (making it negative); otherwise (it's 'Sell'), it uses the price as is (positive).
    • SUM(...): This sums up all the results of the IF statement for each stock. The sum of negative (buy) and positive (sell) prices directly gives the net capital gain/loss.
  3. FROM Stocks: This specifies the table to retrieve data from.
  4. GROUP BY 1: This groups the results by the first column in the SELECT statement (which is stock_name). This ensures that the SUM() function calculates the capital gain/loss separately for each stock.

Time and Space Complexity Analysis

Time Complexity: O(N log N) or O(N), depending on the database engine's query optimization. Grouping and summing operations generally have logarithmic or linear time complexity relative to the number of rows (N) in the Stocks table. The exact complexity depends heavily on the database system's indexing and query optimization strategies.

Space Complexity: O(M), where M is the number of unique stock names. The space required is primarily for storing the intermediate results during the grouping and aggregation steps. This space is proportional to the number of distinct stocks. In the worst case (all rows have unique stock names), M would be equal to N.

Alternative Approaches (Conceptual)

While the above SQL solution is concise and efficient, other approaches are conceivable:

  • Using subqueries: You could use subqueries to separately calculate the total buy price and total sell price for each stock and then subtract them. This would be less efficient than the IF condition within the SUM function.
  • Procedural approach: In some database systems, you might use stored procedures or functions to implement the logic step by step, but this usually adds complexity without improving performance significantly for this specific problem.

The provided SQL solution is generally the most efficient and straightforward way to solve the LeetCode problem.