For the 16th (last) Test Case (Where an extra product_id = 3 is added)
Intuition\nDate: 18-09-2023\n\nThe issue with most common solution is that id does not pass the 15th test case, because we join using product_id and then filter
# Intuition\nDate: 18-09-2023\n\nThe issue with most common solution is that id does not pass the 15th test case, because we join using product_id and then filter out the relevant rows using where clause (using dates). Due to this the third row i.e with product_id == 3 also gets filtered out EVEN IF we use left join, As joins are executed first then followed by the filtering of the where clause. \n\n# Approach\n\nInstead of using where clause to filter out the results, filter out using an addtional condition in the join clause itself. This way when we use left join, the third row (product_id == 3) will be retianed. \n\nWe will also need to apply an IF contion in the select clause to make sure that whenever units are null, it returns zero. \n\nI hope this helps. \n\n# Complexity\n- Time complexity: Not sure.\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity: Not sure. \n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n\n# Code\n```\n# Write your MySQL query statement below\n\nSelect p.product_id, if(sum(units) is not null, round(sum(price*units)/sum(units),2),0) as average_price\nfrom Prices p\nleft join UnitsSold u on (p.product_id = u.product_id AND u.purchase_date between p.start_date and p.end_date)\ngroup by p.product_id\n\n```
['MySQL']