Problem Description –
Write an SQL query 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 query result format is in the following example.
Difficulty Level – Medium
Problem Link – Capital gain and Loss
SELECT stock_name, total_sell - total_buy as capital_gain_loss FROM ( SELECT stock_name, SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END) as total_sell, SUM(CASE WHEN operation = 'Buy' THEN price ELSE 0 END ) as total_buy FROM Stocks GROUP BY 1 ) x ORDER BY 1
In this problem, first we need to calculate the total sell and buy price of each stocks and then subtract the buy from the sell to get the capital gain and loss.