dear leetcode,, give this man a trophy and make him brand ambassador
@EverydayDataScience2 жыл бұрын
Haha, keep up the good work, MLV. Consistency is the key to success. All the best.
@ManasNandMohan10 ай бұрын
Addition of explaining the code with table example is commendable & apprecitable
@GauravKumar-im5zx2 жыл бұрын
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
@EverydayDataScience2 жыл бұрын
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😊
@divjot975 ай бұрын
Bro, you explain the solution and concepts better than leetcode 😉😉🌟🌟🌟😄😄
@prathamsharma4416 Жыл бұрын
most lovely explanation I've ever seen. loved it
@EverydayDataScience Жыл бұрын
Glad that you found the video useful 😊
@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😎
@Slamo282 жыл бұрын
this man carrying the team on his back
@EverydayDataScience2 жыл бұрын
Hopefully you are finding the videos useful.
@abishekbaiju1705 Жыл бұрын
Thanks for making this video. Great way of explaining.
@EverydayDataScience Жыл бұрын
Glad that you liked the video 😊
@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;
@yidan_wang_ittan8 ай бұрын
Thank you so much! you made it so clear!!
@EverydayDataScience8 ай бұрын
Glad that you found the video helpful 😊
@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.
@xzlog674710 ай бұрын
# 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
@tejaswaniguttula59616 ай бұрын
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)?
@ashishgupta-bw8pt Жыл бұрын
great explaination.
@rahulmungali7810 Жыл бұрын
Great explanation 👍👍
@EverydayDataScience Жыл бұрын
Glad that you found the video useful
@ashishgupta-bw8pt Жыл бұрын
Normally I heard that where clause does not work with group by but here how is it working ?
@preethamreddy9 ай бұрын
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
@ejjadaraju5020 Жыл бұрын
can i use avg function? if it not why explain me?
@PURUSHOTTAMJHA-t9h Жыл бұрын
you are best
@nomalware28119 ай бұрын
Great. Why you don't use CTE for this Query ?
@mitulsp8873 Жыл бұрын
one test case is failed by using this query
@kashishtaneja88167 ай бұрын
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
@kavyabanka4482 Жыл бұрын
AMAZING EXPLANATION
@EverydayDataScience Жыл бұрын
Glad that you found the video useful 😊
@darshansr9025 Жыл бұрын
questions says there can be duplicates as well could you please clarify on this
@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 😊
@ay-tw4om Жыл бұрын
amazing!
@mlvprasadofficial2 жыл бұрын
20th one
@MohammedHamed-vj9ey Жыл бұрын
You have to use left outer join to pass all test cases.
@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 ?
@abdulrahmanibrahim7815 Жыл бұрын
AMAZING
@hemantvardani1436 Жыл бұрын
Thanks
@ashmitachakraborty66379 ай бұрын
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.
@ashmitachakraborty66379 ай бұрын
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-gi5wz9 ай бұрын
IFNULL(round(sum(units*price)/sum(units),2),0) this will check for 0 in average column
@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()
@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
@roymou58569 ай бұрын
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
@anirbansatapathi46229 ай бұрын
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.
@arnabsarkar52454 ай бұрын
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..
@rayyanamir85602 жыл бұрын
start a paypal. I will contribute once I start earning.
@chitranshjain97148 ай бұрын
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
@alok27605 ай бұрын
Thank you so much. I tried many things and nothing seemed to be working but this did.
@chitranshjain97145 ай бұрын
Glad that it helped someone!
@HrithickBarani5 ай бұрын
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
@SUJALKACHHADIA2 ай бұрын
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
@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;
@K-EC-AmanKumar11 ай бұрын
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