SQL Interview Questions – Capital Gain and Loss

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

Solution –

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.