EASY SOLUTION | Beats 94.76% | Last Test Case Passed |
Intuition\n Describe your first thoughts on how to solve this problem. \nThe problem requires calculating the average price for each product, taking into accoun
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nThe problem requires calculating the average price for each product, taking into account the units sold and their respective prices. When there are no matching records in the UnitsSold table for a product, the average price should be 0.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\nTo solve this problem, I used a SQL query with a LEFT JOIN between the Prices and UnitsSold tables. This join allows me to include all products from the Prices table, even if there are no matching records in the UnitsSold table. I calculated the average price for each product using the ROUND and SUM functions, and I used IFNULL to replace null values with 0 in cases where there are no matching records.\n\n# Complexity\n- Time complexity:\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\nThe time complexity of this query depends on the number of rows in both the Prices and UnitsSold tables. Let\'s denote the number of rows in Prices as "n" and the number of rows in UnitsSold as "m." The time complexity can be considered as O(n + m) because the query involves aggregating data from both tables.\n\n- Space complexity:\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\nThe space complexity of this query is primarily determined by the memory required to store the result set, which consists of one row per product with the product_id and average_price columns. Therefore, the space complexity is O(n), where "n" is the number of products in the Prices table.\n\n# Code\n```\n# Write your MySQL query statement below\nselect Prices.product_id, ifnull(round(sum(units*price)/sum(units),2),0) as average_price\nfrom Prices LEFT JOIN UnitsSold\non Prices.product_id = UnitsSold.product_id and UnitsSold.purchase_date between Prices.start_date and Prices.end_date\ngroup by Prices.product_id;\n```
['Database', 'MySQL']