MySQL Solution to Calculate Average Selling Price
Intuition\n Describe your first thoughts on how to solve this problem. \nWe need to find the average selling price for each product, which can be solved by calc
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nWe need to find the average selling price for each product, which can be solved by calculating the total price of each product sold and dividing it by the total number of units sold. \n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n1. Select specific columns from the table.\n\n    > `SELECT` \u2192 This command retrieves data from a database\n\n    > `AS` \u2192 This command renames a column with an alias (temporary name). In most database languages, we can skip the `AS` keyword and get the same result\n\n2. Calculate the average selling price for each product.\n\n    > `SUM()` \u2192 This function returns the total sum of a numeric column\n\n    > `ROUND(number, decimal)` \u2192 This function rounds a `number` to a specified number of `decimal` places\n\n    > `IFNULL(expression, alt_value)` \u2192 This function returns the `alt_value` if the `expression` is `NULL`\n\n3. Combine other tables to check if the `purchase_date` falls within the date range defined by `start_date` and `end_date`.\n\n    > `LEFT JOIN` \u2192 This command returns all rows from the left table (table 1), and the matching rows from the right table (table 2). The result is `NULL` from the right side, if there is no match.\n\n    > `ON` \u2192 This allows to specify the column names for join keys in both tables. It is used to join tables where the column names don\u2019t match in both tables.\n\n    > `BETWEEN` \u2192 This operator selects values within a given range (it is inclusive: begin and end values are included).\n\n    > `AND` \u2192 This operator filters records based on more than one condition\n\n4. Group the results based on specific columns.\n\n    > `GROUP BY` \u2192 This command groups rows that have the same values into summary rows, typically to perform aggregate functions on them\n\n# Complexity\n- Time complexity: $O(n)$\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n`n` is the number of rows in the joined result. This is because the query processes each row in the table once to calculate the average price for each product.\n\n# Code\n```\nSELECT \n    product_id, \n    IFNULL(ROUND(SUM(prices_sum) / SUM(units), 2), 0) AS average_price\nFROM (\n    SELECT \n        p.product_id AS product_id, \n        units, \n        price * units AS prices_sum\n    FROM Prices p \n    LEFT JOIN UnitsSold u ON p.product_id = u.product_id AND purchase_date BETWEEN start_date AND end_date\n    ) AS temp\nGROUP BY product_id;\n```
['MySQL']