The Stocks table schema is defined as follows:

The primary key for this table is the combination of stock_name and operation_day. The operation column is an enumeration containing ('Buy', 'Sell'). Each row represents a transaction made on a specific stock at a given price.

The objective is to calculate the total capital gain or loss for each stock. The capital gain or loss is defined as the difference between the total selling price and the total purchase price for a specific stock.

Example

Input:

Output:

Explanation:

• Leetcode: Bought for 1000 and sold for 9000. Profit = 8000.
• Handbags: Bought for 30000 and sold for 7000. Loss = -23000.
• Corona Masks: Multiple transactions occurred. Net result = (1010-10) + (500-1000) + (10000-1000) = 9500.

Solution

To determine the profit or loss, we can aggregate the price values for each stock. 'Buy' operations represent costs (negative value), while 'Sell' operations represent revenue (positive value). The sum of these values yields the net capital gain or loss.

Option 1: Using CASE expression

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

Option 2: Using IF function

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