LeetCode 1393: Capital Gain/Loss [SQL]

Solution and walkthrough of leetcode database problem 1393: Capital Gain/Loss I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

Playlists:

[YASHKUMARJAIN]

select stock_name,
sum(case when operation="Sell" then price else -price end ) as capital_gain_loss
from Stocks
group by stock_name

[jajatibehera]

You are the best so far in the youtube explaining leetcode sql.The way and approach to the solution has been phenomenal. Keep bringing more video like this.Great job and well appreciated !

[ThinkBigToday]

I like the stock. And, I like your Channel. I have seen all the videos, can't thank you enough!

[ajaxaj]

Self join stock name with one table buy and other sell...

[shammi]

with abc as (

select

stock_name,

sum(case when operation = 'Buy' then price end) as buy_price,

sum(case when operation = 'Sell' then price end) as sell_price

from stocks

group by 1

)



select stock_name,

sell_price - buy_price as capital_gain_loss

from abc

[onePunch]

my sol:
with actual_price as (select (if(operation='Buy', -1*price, price)) as price, stock_name
from stocks)
select stock_name, sum(price) as capital_gain_loss
from actual_price
group by stock_name
order by capital_gain_loss desc

[karthikbs]

SELECT stock_name, SUM(price -- previous_buy) as capital_gain_loss
FROM(
SELECT *, LEAD(price) OVER (PARTITION BY stock_name ORDER BY operation_day DESC) as previous_buy
FROM Stocks
) AS temp
WHERE temp.operation='Sell'
GROUP BY stock_name

My solution