LeetCode 1251 Interview SQL Question with Detailed Explanation | Practice SQL

[@mlvprasadofficial]

dear leetcode,, give this man a trophy and make him brand ambassador

[@EverydayDataScience]

Haha, keep up the good work, MLV. Consistency is the key to success. All the best.

[@ManasNandMohan]

Addition of explaining the code with table example is commendable & apprecitable

[@GauravKumar-im5zx]

you will not trust me that I spent hours for this problem and came up with a solution which was not correct. Whereas, while I was watching your video, I did not even realize that when the problem got solved. Fantastic way of explaining and I was flowing along with your explanation .You made it so simple. Thanks sir

[@EverydayDataScience]

Glad that the video was helpful, Gaurav.

[@EUEC_CHARRUNETHRACR]

Thanks for the fabulous video, very useful. Whenever I take SQL in leetcode, I will follow your playlist. Thanks for the awesome video ever :)

[@EverydayDataScience]

Glad that you are finding the videos useful 😊 Cheers!

[@wishimaunicorn]

I am telling you, I owe you my life for this. You are a the god of SQL

[@EverydayDataScience]

Woah😁!! Those are some very high praise words. I’m sure there are better ways to solve this. But I’m glad that you found the video useful. Thank you for such kind words😊

[@prathamsharma4416]

most lovely explanation I've ever seen. loved it

[@EverydayDataScience]

Glad that you found the video useful 😊

[@mitulsp8873]

one test case is failed by using this query

[@Slamo28]

this man carrying the team on his back

[@EverydayDataScience]

Hopefully you are finding the videos useful.

[@divjot97]

Bro, you explain the solution and concepts better than leetcode 😉😉🌟🌟🌟😄😄

[@vamshikrishna7039]

really superbbbbbb , keep going bro ur explaination is very good eventhouh u can wrap up in 10 minutes ,but u dont do this , 1 subscriber added😎

[@gauravgaikwad2939]

Its failing last tast case where there is one entry in prices table but not in unitssold table. Need to use ifnull to pass all the test cases.

[@xzlog6747]

# Write your MySQL query statement below select p.product_id,IFNULL(ROUND(SUM(p.price*u.units)/SUM(u.units),2),0) as average_price from Prices p left join UnitsSold u on p.product_id=u.product_id and u.purchase_date between p.start_date AND p.end_date Group by product_id; IFNULL is not included in the video

[@tejaswaniguttula5961]

​​Hi ​@@xzlog6747 I really need a clarification from you that while performing inner join it should consider only the product_id which is common in both prices table and unitssold table then why it's including the product_id from prices which is actually not there in unitssold (failed testcase)?

[@vaishnavibodake2838]

have to use---- coalesce( ROUND(SUM(p.price * u.units) / SUM(u.units), 2),0) AS average_price to avoid failing from all side test cases. otherwise all explaination is perfect sir. .. 😌

[@iamabishekbaiju]

Thanks for making this video. Great way of explaining.

[@EverydayDataScience]

Glad that you liked the video 😊

[@yidan_wang_ittan]

Thank you so much! you made it so clear!!

[@EverydayDataScience]

Glad that you found the video helpful 😊

[@mickyman753]

came up with my own soltuion but it took a lot of time for a easy problem : with cte as ( select price,units,p.product_id as product_id,case when units is null then 0 else units end as newunits from prices p left join UnitsSold u on p.product_id=u.product_id and (purchase_date between start_date and end_date) ) select product_id,round(sum(newunits*price)/if(sum(newunits)=0,1,sum(newunits)),2) as average_price from cte group by product_id;

[@rahulmungali7810]

Great explanation 👍👍

[@EverydayDataScience]

Glad that you found the video useful

[@ashishgupta-bw8pt]

great explaination.

[@vamshikrishna7039]

supeerr explaination but i understand joins at a good level, but u can give same explaiation to group by brooo

[@containthis4450]

great explanation !!!! yay

[@EverydayDataScience]

Glad that you found the video useful 😊

[@nomalware2811]

Great. Why you don't use CTE for this Query ?

[@preethamreddy]

it is accepted for me but only 16 test cases have passed when I checked the test case it said id= 3 avg_price=0 so very confused with this output

[@ashishgupta-bw8pt]

Normally I heard that where clause does not work with group by but here how is it working ?

[@ay-tw4om]

amazing!

[@PURUSHOTTAMJHA-t9h]

you are best

[@darshansr9025]

questions says there can be duplicates as well could you please clarify on this

[@chitranshjain9714]

with new test cases only 15 will pass hence we have to include LEFT JOIN the code is provided below SELECT p.product_id AS product_id , CASE WHEN u.units is NOT NULL THEN ROUND(SUM(p.price*u.units)/SUM(u.units),2) ELSE 0 END AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id=u.product_id WHERE u.purchase_date BETWEEN p.start_date AND p.end_date OR u.purchase_date IS NULL GROUP BY p.product_id

[@alok2760]

Thank you so much. I tried many things and nothing seemed to be working but this did.

[@chitranshjain9714]

Glad that it helped someone!

[@adityasaini8437]

I don't understand your logic on inner join you applied? Like as the definition says join gets perform on matching values and the no rows in out put after inner join need to be 4 only and in your case it's 8 ?

[@kavyabanka4482]

AMAZING EXPLANATION

[@EverydayDataScience]

Glad that you found the video useful 😊

[@ashmitachakraborty6637]

Hey, I tried this and seems like it only passed 15 test cases for me instead of all 17. There's a 0 that's showing up in the average selling price column.

[@ashmitachakraborty6637]

I tried wrapping your code in a CTE and then using a new select statement to call it, I tried using where the average isn't 0 but getting the error - Unknown column 'average_price' in 'where clause'. How can I resolve this?

[@AdityaSingh-gi5wz]

IFNULL(round(sum(units*price)/sum(units),2),0) this will check for 0 in average column

[@ejjadaraju5020]

can i use avg function? if it not why explain me?

[@kashishtaneja8816]

sir what is the issue wit this SELECT p.product_id, ROUND(SUM(p.price * u.units) / SUM(u.units), 2) as average_price FROM Prices p LEFT JOIN Unitssold u ON p.product_id = u.product_id AND u.purchase_date

[@MohammedHamed-vj9ey]

You have to use left outer join to pass all test cases.

[@hemantvardani1436]

Thanks

[@Karan-zk9ke]

this code now is not working , maybe the test cases have been changed

[@ulisesfranciscofonsecamart8975]

DO U FIND THE SOLUTION?

[@karansumbly1254]

@@ulisesfranciscofonsecamart8975 Select p.product_id, CASE WHEN u.product_id IS NULL THEN 0 ELSE Round(SUM(p.price*u.units)/SUM(u.units),2) END AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id=u.product_id Where (u.purchase_date BETWEEN p.start_date and p.end_date) OR u.product_id is NULL #product never sold Group by p.product_id

[@sauravchandra10]

Yes @@ulisesfranciscofonsecamart8975

[@ashishsuman7079]

same code ...working on run ..but 1 testcase failed on submit

[@firzainsanudzaky3763]

i think its leet code problem, since there is no 3 in the leet code

[@firzainsanudzaky3763]

So just add ifnull before round()

[@abdulrahmanibrahim7815]

AMAZING

[@mlvprasadofficial]

20th one

[@roymou5856]

now it needs a left join to pass new test cases. select p.product_id, IFNULL(round(sum(price*units)/sum(units),2),0) as average_price from Prices p left join UnitsSold u on p.product_id = u.product_id and u.purchase_date between p.start_date and p.end_date group by p.product_id But can anyone tell me how is it wrong if i write where instead of and in the below query: select p.product_id, IFNULL(round(sum(price*units)/sum(units),2),0) as average_price from Prices p left join UnitsSold u on p.product_id = u.product_id where u.purchase_date between p.start_date and p.end_date group by p.product_id

[@anirbansatapathi4622]

The first query retains all rows from the Prices table, even if there are no corresponding units sold meeting the condition. The second query only retains rows from the Prices table that have corresponding units sold meeting the condition.

[@arnabsarkar5245]

Hello sir, i wrote the exact same query : select p.product_id, round(sum(u.units * p.price) / sum(u.units), 2) as average_price from Prices p inner join UnitsSold u on p.product_id = u.product_id where u.purchase_date between p.start_date and p.end_date group by p.product_id But it is failing from the 15th test case. I though my query is wrong, so i watched your solution, but my and yours query is exactly same. test case : Prices : | product_id | start_date | end_date | price | | ---------- | ---------- | ---------- | ----- | | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | | 3 | 2019-02-21 | 2019-03-31 | 30 | Unitssold: | product_id | purchase_date | units | | ---------- | ------------- | ----- | | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | My Output : | product_id | average_price | | ---------- | ------------- | | 1 | 6.96 | | 2 | 16.96 | Expected Output : | product_id | average_price | | ---------- | ------------- | | 1 | 6.96 | | 2 | 16.96 | | 3 | 0 | Basically we are taking inner join because we want that every value from prices table should come, but in your query it will not be possible because we are filtering on the basis of dates. Can anyone please help me with this..

[@rayyanamir8560]

start a paypal. I will contribute once I start earning.

[@HrithickBarani]

If Somebody is getting stuck while submitting at 16th Testcase, Please use below Solution: select p1.product_id, coalesce(round(sum(units*price)/sum(units),2),0) as average_price from Prices p1 left join UnitsSold p2 on p1.product_id=p2.product_id where (purchase_date between start_date and end_date) or purchase_date is null group by p1.product_id

[@SUJALKACHHADIA]

Right answers :- SELECT p.product_id, IFNULL(ROUND(sum(p.price * u.units) / sum(u.units), 2),0) AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date GROUP BY p.product_id

[@mickyman753]

the question has now new test cases so the solution of the video won't work now

[@K-EC-AmanKumar]

SELECT p.product_id, IFNULL(ROUND(SUM(p.price*u.units)/SUM(u.units),2),0) AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date GROUP BY p.product_id

[@ritwiksingh5943]

Try this instead, it will cover the case of a test case wherein the product id is there in prices table but not in units sold table: SELECT p.product_id, ROUND(SUM(p.price * IFNULL(u.units, 0)) / IFNULL(SUM(u.units), 1), 2) AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id WHERE u.purchase_date BETWEEN p.start_date AND p.end_date OR u.purchase_date IS NULL GROUP BY p.product_id;