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

[@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

[@roymou5856]

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

[@sivasrimakurthi206]

I like the way, you simplified this. Clear and more logical. Thanks roy!!

[@ritusantra8641]

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;

[@adityams1659]

*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;

[@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

[@muhammadzakiahmad8069]

Nixe solution

[@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?

[@neosunny2706]

What is the core topic behind this Question window function?

[@kabirmehra6933]

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.

[@rohanpatil7364]

What are errors tell here look for the new test cases which may be helpful the above solution looks correct to me atleast

[@saratchandra9948]

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

[@SyedShadabHussaini1996]

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?

[@EverydayDataScience]

If you can get Datacamp subscription, it’s one of the best source to learn SQL & Python. Annual cost is I think $149. If you don’t want to pay for the subscription, then there are many books/youtube videos with most of the concepts of SQL. One great website is w3schools, it has very detailed SQL knowledge with examples.

[@SyedShadabHussaini1996]

@@EverydayDataScience Thanks for your response! Would you consider making tutorials yourself in the future?

[@EverydayDataScience]

@@SyedShadabHussaini1996 Yes, definitely. In fact I have 4 videos already th-cam.com/play/PLtfxzVLWb-B-F9LSIiZBb9XitfUX4rln9.html

[@SyedShadabHussaini1996]

@@EverydayDataScience Perfect. Just what I was looking for!

[@mlvprasadofficial]

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

[@ankanchakraborty3309]

Simple solution with group by and sum and if statement. You can use case when statements as well instead of if statement if that makes you more comfortable..
Select sale_date, if(fruit = 'oranges',sum( - sold_num),sum(sold_num)) as diff group by sale_date order by select sale_date

[@muddassirnazar9275]

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

[@darshshah9566]

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

[@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;

[@RajKamal-vf6xq]

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)) as diff
from sales1445
group by sale_date
order by sale_date asc

[@RajKamal-vf6xq]

with cte as (
select *
, LEAD(sold_num) over(partition by sale_date order by fruit) as orange_sold_num
from sales1445)
select sale_date
, SUM((sold_num-orange_sold_num)) as diff
from cte
group by sale_date
order by sale_date

[@muhammadzakiahmad8069]

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';

[@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 ?

[@NeelShah31o1]

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

[@mlvprasadofficial]

44

[@sukumar-m4t]

Leet Premium

[@madhuryadav4316]

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.fruits2.fruit
and s1.fruit='apples'

[@nabinagoswami1261]

WITH Final As (
WITH Results AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY sale_date,fruit) as RNK
FROM `leetcode-questions.LEETCODE.Sales_44`
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

[@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;