LeetCode Medium 1445 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

[expandingourselves]

with cte as (
select * from Sales where fruit = 'apples'
), cte2 as (
select * from Sales where fruit = 'oranges'
)

select a.sale_date, sum(a.sold_num - b.sold_num) as diff from cte a
inner join cte2 b on a.sale_date = b.sale_date
group by 1
order by 1 asc

[roymou]

with cte as
(
select sale_date , sum(case when fruit = "apples" then (sold_num) else 0 end) as appleCnt,
sum(case when fruit = "oranges" then (sold_num) else 0 end) as orangeCnt
from Sales
group by sale_date
)

select sale_date, (appleCnt - orangeCnt) as diff
from cte

[AbhijitPaldeveloper]

select sale_date, sum(case when fruit = 'apples' then sold_num when fruit = 'oranges' then -sold_num end) as diff
from Sales
group by sale_date
order by sale_date

[adityams]

*My Approach with single CTE*

WITH cte AS (
SELECT
sale_date,
sum(sold_num) FILTER (WHERE fruit = 'apples') AS apples_sold,
sum(sold_num) FILTER (WHERE fruit = 'oranges') AS oranges_sold
FROM
Sales
GROUP BY
sale_date
)
SELECT
sale_date,
apples_sold - oranges_sold AS diff
FROM
cte
ORDER BY
sale_date;

[ritusantra]

with cte as (select
sale_date,
max(case when fruit = 'apples' then sold_num end) as app,
max(case when fruit = 'oranges' then sold_num end) as org
from sales
group by sale_date)
select sale_date, app - org as diff
from cte;

[diwakarisonyoutube]

SELECT sale_date, SUM(CASE fruit WHEN 'apple' THEN sold_num WHEN 'oranges' THEN -1*sold_num END) AS diff
FROM Sales
GROUP BY sale_date;

Does this work?

[kabirmehra]

Hi Sir!
Really love your work and your way of teaching.

Just one questions - why does the code for this question not work now? I have been trying to debug every part of it and all the testcases can't be passed.

[SyedShadabHussaini]

Hello. Thanks for this

Unrelated to the video, but I am planning to start learning SQL. Can you please recommend any beginner friendly courses/books for me to get started?

[saratchandra]

select to_char(sale_date, 'YYYY-MM-DD') AS SALE_DATE, sum(case when fruit='apples' then sold_num else 0 end)-sum(case when fruit='oranges' then sold_num else 0 end) as diff
from sales group by sale_date order by sale_date

[neosunny]

What is the core topic behind this Question window function?

[mlvprasadofficial]

in sql server, order by in cte doesnt work, what to do then ?

[darshshah]

Bro, I am having a hard time solving Leetcode problem - 1767 Can you try to upload a video based on that question?

[muddassirnazar]

Easiest Solution:select sale_date,
sum(case when fruit='apples' then sold_num else -1*sold_num end) as diff
from sales
group by sale_date

[NagaCheemakurthy]

select a.sale_date, (a.qty_sold-b.qty_sold) as diff
from sales_data a
join (Select * from sales_data where fruit='Orange') b
on a.sale_date = b.sale_date
where a.fruit='apple'
order by 1;

[SaiPhaniRam]

I came up with this solution (self join):

SELECT sale_date, (s1.sold_num - s2.sold_num) AS diff
FROM Sales s1
INNER JOIN Sales s2
ON s1.sale_date = s2.sale_date
WHERE s1.fruit <> s2.fruit
AND s1.fruit = 'apples'

May I know, if it is less performant by any chance ?

[NeelShaho]

SELECT
sale_date,
SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE 0 END) -
SUM(CASE WHEN fruit = 'oranges' THEN sold_num ELSE 0 END) diff
FROM sales
GROUP BY sale_date
ORDER BY sale_date

[muhammadzakiahmad]

WITH cte AS (
SELECT sale_date,
fruit,
sold_num,
LEAD(sold_num) OVER (PARTITION BY sale_date ORDER BY fruit) AS next_sold_num
FROM sales
)
SELECT *,
sold_num - COALESCE(next_sold_num, 0) AS difference
FROM cte
WHERE fruit = 'apples';

[madhuryadav]

another solution?

select s1.date, abs(s1.sold_num - s2.sold_num)
from sales s1
cross join sales s2
on s1.sale_date = s2.sale_date
where s1.fruit<>s2.fruit
and s1.fruit='apples'

[nabinagoswami]

WITH Final As (
WITH Results AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY sale_date, fruit) as RNK
FROM
ORDER BY sale_date, fruit )

SELECT S1.sale_date, S1.fruit, S1.sold_num AS Sold_NUM_1, S2.fruit, S2.sold_num AS Sold_NUM_2
FROM Results S1
JOIN Results S2
ON S1.sale_date=S2.sale_date AND S1.fruit="apples" AND S2.fruit="oranges" AND S1.RNK<S2.RNK
ORDER BY 1, 2, 4
)

SELECT sale_date, (Sold_NUM_1-Sold_NUM_2)as DIFF
FROM Final

[NagaCheemakurthy]

select a.sale_date, (a.qty_sold-b.qty_sold) as diff
from sales_data a
join (Select * from sales_data where fruit='Orange') b
on a.sale_date = b.sale_date
where a.fruit='apple'
order by 1;