100 % || Easy && Intuitive || SQL Query || Using LEFT JOIN || Beats 99.91 % || ( Well - Explained )
Intuition\n Describe your first thoughts on how to solve this problem. \nSELECT p.product_id, --> Show product_id column from Prices table...\nIFNULL(ROUND(SUM(
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nSELECT p.product_id, --> Show product_id column from Prices table...\nIFNULL(ROUND(SUM(p.price*u.units)/SUM(u.units),2),0) --> if average is null replace it with 0 && round is used to round of average up to 2 decimal places...\nAS average_price --> We took alias as average_price...\nFROM Prices p --> Association Prices table alias as p...\nLEFT JOIN --> it helps to join the two tables...\nUnitsSold u --> UnitsSold table aliad as u...\nON p.product_id = u.product_id --> joining condition...\nAND u.purchase_date >= p.start_date \nAND u.purchase_date <= p.end_date --> Conditions given in Problem...\nGROUP BY p.product_id; --> w.r.t. product_id...\n# Approach\n<!-- Describe your approach to solving the problem. -->\nUsing fundamentals of SQL...\n# Complexity\n- Time complexity: O(n)\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity: O(1)\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n\n# Code\n```\nSELECT p.product_id, \nIFNULL(ROUND(SUM(p.price*u.units)/SUM(u.units),2),0) \nAS average_price \nFROM Prices p \nLEFT JOIN \nUnitsSold u \nON p.product_id = u.product_id \nAND u.purchase_date >= p.start_date \nAND u.purchase_date <= p.end_date \nGROUP BY p.product_id;\n```
['Database', 'MySQL']