Average Selling Price | Leetcode 1251 | Crack SQL Interviews in 50 Qs

[@learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on Instagram Handles :- @createwithchirag - instagram.com/createwithchirag/ @learn.with.chirag - instagram.com/learn.with.chirag/ LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/ Let's stay connected and keep the creativity flowing! 💡

[@chiragbhundiya6652]

We can also use BETWEEN to check date range instead of >= and

[@negative_seaweed]

prodcut_id is not a foreign key in UnitsSold table , which means we can have a product_id which is mentioned in Prices but not in UnitsSold that's why we are using Left Join, otherwise inner Join would have worked fine.

[@kanhashukla6265]

This solution leave 2 test cases where there is a product with no purchase. Correct solution: select DISTINCT pr.product_id, coalesce(average_price,0) average_price from Prices pr left join ( select p.product_id, round(cast(sum(price*units) as float)/sum(units),2) 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 ) A on pr.product_id = A.product_id

[@ikhariyafaizal6281]

I took few minutes to solve this but couldn't manage to solve 2 test cases which were regarding null issue. Should have seen your video earlier.

[@learnwithchirag]

Check all the videos ! You will love the detailed explanation of each query !

[@harshraaz4025]

Hi Chirag, When I am using below code then few test cases are failed: select p.product_id, ifnull(round(SUM(price*units)/sum(units),2) ,0)as average_price from prices p left join unitsSold s ON p.product_id =s.product_id where purchase_date between start_date and end_date group by 1 if I use' and purchase_date between start_date and end_date' then it passes all tests Can u pls suggest why "where" condition is failed and "AND" is passed?

[@samarthbillava4346]

Same

[@adityashrivastav3148]

can we use between clause into this ?

[@mdamirhussain2095]

Why Left join .... i have applied inner join .... but solution not submitted .... I am unable to know ..... whats the reason behind

[@sandeepvarma89]

Bhai ek doubt tha sum(u.units)par bhi condition apply hogi na toh voh 115 kese aaya kyunki agar p.product_id=u.product_id ye aur start aur end date dono satisfy hoga tab sum hoga na ?

[@AshwinGhosalkar]

Hi, For MS SQL Server. SELECT U.product_id, ISNULL(round((SUM(p.price*u.units*1.00)/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 U.product_id whats wrong in this query? anyone?

[@lakshitagarg2600]

IFNULL ayega

[@buzzfeedRED]

Hi Why this solution wrong Sir! ` 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 where u.purchase_date >= p.start_date AND u.purchase_date

[@ganeshvhatkar9040]

The problem in the query lies in the WHERE clause. The WHERE clause filters out rows where s.purchase_date is NULL, which means that if there are no corresponding sales in the UnitsSold table for a product, the product will be excluded from the result set. To ensure that all products from the Prices table are included, you need to move the date conditions from the WHERE clause to the ON clause of the LEFT JOIN. This way, products without sales will still be included in the result, and their average price will be calculated correctly as 0. Ask query related questions to the ChatGPT bro

[@himanshuranjan657]

Why inner join dosent work?

[@learnwithchirag]

In this query, an 'INNER JOIN' is not used because it would exclude products from the result set if there are no matching records in the UnitsSold table for a given product in the Prices table. Using a 'LEFT JOIN' ensures that all records from the Prices table are included in the result set, regardless of whether there are matching records in the UnitsSold table. This is because a 'LEFT JOIN' returns all rows from the left table (Prices) and the matched rows from the right table (UnitsSold), with NULL values in the columns from the right table where there is no match.

[@AbhishekTiwari-bk4bt]

SIR Can we use USING PRODUCT_ID INSTEAD OF ON P.PRODUCT_ID = U.PRODUCT_ID I TRIED USING THE FIRST SYNTAX but it shows syntax error

[@pranaymathur9272]

purvesh op

[@skkrrtt2344]

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