LeetCode Medium 1393 Interview SQL Question with Detailed Explanation | Practice SQL

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[EverydayDataScience]

Another solution:

WITH cte AS
(SELECT *
FROM Stocks
ORDER BY stock_name, operation, operation_day),

cte2 AS
(SELECT *, LEAD(price) OVER(PARTITION BY stock_name) AS selling_price
FROM cte)

SELECT stock_name, SUM(selling_price - price) AS capital_gain_loss
FROM cte2
WHERE operation = 'Buy'
GROUP BY stock_name

[nitinmadan]

I had created two CTE's each for buy as well as sell to create respective sum of buy & sell prices based on grouping of stocks and then joining the two CTE's to subtract the buy and sell columns- But your solution was super smooth, simple and intuitive. Thanks!!

[prateekmohan]

with cte as (
select *,
LEAD(price, 1, null) over (partition by stock_name order by operation_day) as selling_price from stocks )

,
cte2 as (
select *, selling_price - price as profit from cte
where operation = 'buy' )

select stock_name, sum(profit) as capital_gain_loss from cte2
group by stock_name
order by sum(profit)

[hdr-tech]

Alternate lenghy solution

with cte as (
select *
, sum(price) over(partition by stock_name, operation order by stock_name) as ee
from Stocks
),
cte1 as (
select stock_name,
max(case when operation='Buy' then ee end) as Buy,
max(case when operation='Sell' then ee end) as Sell
from cte
group by stock_name
)
select stock_name
, (Sell - Buy) as capital_gain_loss
from cte1

[NeelShaho]

Most Simple And Easy To Understand Solution

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

[KARNKUMAR-xb]

Your explanation is mind blowing ( as always ) and my solution is with same logic with CTE is here -->
WITH cte AS
(SELECT DISTINCT stock_name, SUM(price) AS B
FROM Stocks
WHERE operation = 'Buy'
GROUP BY stock_name),

cte2 AS
(SELECT DISTINCT stock_name, SUM(price) AS S
FROM Stocks
WHERE operation = 'Sell'
GROUP BY stock_name)

SELECT c1.stock_name, (c2.S - c1.B) AS capital_gain_loss
FROM cte c1
JOIN cte2 c2
WHERE c1.stock_name = c2.stock_name

Thank u :)

[metricsview]

SELECT
stock_name,
( SUM(CASE WHEN operation = 'Sell' THEN price ELSE 0 END)-SUM(CASE WHEN operation = 'Buy' THEN price ELSE 0 END)
) as diff
FROM Stocks
GROUP BY stock_name;

[k_aryal]

with cte as (select *, rank() over(partition by stock_name order by operation) as rn
from stocks)

select stock_name, sum(case when operation ='Buy' then -price else price end) as capital_gain_loss
from cte
group by stock_name

[harshavardhanmadasi]

Is there any full course of sql, that you can recommend to watch before solving problem

[mickyman]

I did not got this simple thinking that we just need to put all buys and sell together and take thier difference this this operation day is just useless data

what I thought was using this opeartion day and use lead window function and thought one will buy and then sell, not that he can buy multiple times before selling, so lead logic will fail, my wrong solution : with cte1 as
(select *, lead(price, 1) over(partition by stock_name order by operation_day)as
next_day_price,
lead(opeartion, 1) over(partition by stock_name order by operation_day)as
next_day_opeartion from Stocks)

select stock_name, sum(next_day_price-price) as capital_gain_loss
from cte1
where operation='Buy'
group by stock_name ;

[divyanshusinghchauhan]

with cte1 as (

select *,
Lead(nextDayPrice, 1, 0) OVER(PARTITION BY t.stock_name ORDER BY t.operation) AS nextDayRate,
nextDayPrice - Lead(nextDayPrice, 1, 0) OVER(PARTITION BY t.stock_name ORDER BY t.operation) AS capital_gain_loss
from (

SELECT
stock_name, operation,
sum(price) OVER(PARTITION BY stock_name, operation) AS nextDayPrice
FROM
stocks
)t group by t.stock_name, t.operation
)

select stock_name, capital_gain_loss from cte1 where operation = 'Sell'