LeetCode 1251 Interview SQL Question with Detailed Explanation

LeetCode 1251 Interview SQL Question with Detailed Explanation | Practice SQL

Рет қаралды 10,692

Күн бұрын

Previous Video: LeetCode 1241 Comments Per Post
• LeetCode 1241 Intervie...
In this video I solve and explain a 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 is about average selling price and also includes points to keep in mind to develop SQL queries. You will also learn about SQL JOINS, GROUP BY and BETWEEN clause.
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

Пікірлер: 64

@mlvprasadofficial 2 жыл бұрын

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

@EverydayDataScience 2 жыл бұрын

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

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

@divjot97 3 ай бұрын

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

@mitulsp8873 11 ай бұрын

one test case is failed by using this query

@Slamo28 2 жыл бұрын

this man carrying the team on his back

@EverydayDataScience 2 жыл бұрын

Hopefully you are finding the videos useful.

@arnabsarkar5245 2 ай бұрын

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..

@gauravgaikwad2939 10 ай бұрын

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 7 ай бұрын

# 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 4 ай бұрын

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)?

@PURUSHOTTAMJHA-t9h Жыл бұрын

you are best

@ay-tw4om Жыл бұрын

amazing!

@K-EC-AmanKumar 8 ай бұрын

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

@MohammedHamed-vj9ey 10 ай бұрын

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

@kavyabanka4482 Жыл бұрын

AMAZING EXPLANATION

@EverydayDataScience Жыл бұрын

Glad that you found the video useful 😊

@mlvprasadofficial 2 жыл бұрын

20th one

@adityasaini8437 11 ай бұрын

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 ?

@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 11 ай бұрын

Yes @@ulisesfranciscofonsecamart8975

@vamshikrishna7039 9 ай бұрын

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😎

@ashmitachakraborty6637 7 ай бұрын

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 7 ай бұрын

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 7 ай бұрын

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

@chitranshjain9714 5 ай бұрын

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 3 ай бұрын

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

@chitranshjain9714 3 ай бұрын

Glad that it helped someone!

@vamshikrishna7039 9 ай бұрын

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

@hemantvardani1436 Жыл бұрын

Thanks

@ashishgupta-bw8pt Жыл бұрын

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

@HrithickBarani 2 ай бұрын

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

@kashishtaneja8816 5 ай бұрын

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

@nomalware2811 6 ай бұрын

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

@ritwiksingh5943 9 ай бұрын

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;

@ManasNandMohan 7 ай бұрын

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

@ejjadaraju5020 9 ай бұрын

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

@preethamreddy 7 ай бұрын

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

@darshansr9025 Жыл бұрын

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

@ashishgupta-bw8pt Жыл бұрын

great explaination.

@abishekbaiju1705 11 ай бұрын

Thanks for making this video. Great way of explaining.

@EverydayDataScience 11 ай бұрын

Glad that you liked the video 😊

@wishimaunicorn 11 ай бұрын

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

@EverydayDataScience 11 ай бұрын

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😊

@yidan_wang_ittan 5 ай бұрын

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

@EverydayDataScience 5 ай бұрын

Glad that you found the video helpful 😊

@roymou5856 7 ай бұрын

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 7 ай бұрын

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.