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! 💡
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.
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
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?
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 ?
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?
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.
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
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
![LeetCode 1934: Confirmation Rate [SQL]](http://i.ytimg.com/vi/_St_otcib-U/mqdefault.jpg)
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! 💡
We can also use BETWEEN to check date range instead of >= and
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.
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
can we use between clause into this ?
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?
Same
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.
Check all the videos ! You will love the detailed explanation of each query !
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 ?
Why Left join .... i have applied inner join .... but solution not submitted .... I am unable to know ..... whats the reason behind
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?
IFNULL ayega
purvesh op
Why inner join dosent work?
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.
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
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