🧠 Simple PostgreSQL solution 🧠 Beats 96.93% || SUM, OUTER JOIN, COALESCE ||
Solution\n\n- To get the average selling price all we need to know is:\n    1.      total number of sold units of each product\n    2.      How much money we\'v
# Solution\n\n- To get the average selling price all we need to know is:\n    1. &nbsp;&nbsp;&nbsp;&nbsp; total number of sold units of each product\n    2. &nbsp;&nbsp;&nbsp;&nbsp; How much money we\'ve made overall selling said product\n\n- With this information we can simply divide **amount of money made** by **total number of sold units**. \n## Ok, but how do we get this info?\n- To get the idea how much money we\'ve made we have to first understand that for a different time period there may be a different price, so we cannot simply `JOIN` Prices and UnitsSold together by `product_id` and call it a day: we have to do it by adding an **additional** condition which will `JOIN` only those UnitsSold\'s rows to Prices\' rows which `purchase_date` is between Prices\' `start_date` and `end_date`. But is `INNER JOIN` Ok? No, because there may be products which didn\'t sell at all so we have to use an `OUTER JOIN` \n    ```   \n    Prices p LEFT JOIN \n    UnitsSold u ON \n    p.product_id = u.product_id AND \n    u.purchase_date BETWEEN p.start_date AND p.end_date\n    ```\n- After we got our table, we need to separate different products from each other, and group same products, regardless of their time period. We can do this like this:\n```\nGROUP BY \n    p.product_id;\n```\n- All right, we have our table and so do we have our groups. Now we can calculate - as I said at the beginning - how much money we have made from a product by summing up amount made from each time period. How this happens? \n    - Remember how we joined rows from Prices and UnitsSold? As the result we got something like the table below. Each `product_id` is grouped according to their value. To calculate amount of money made from each period for each group, we can simply do `u.units * p.price` for each and every row, summing them up by surrounding them by aggregate function `SUM()` \n```\nSUM(u.units * p.price)\n```\n   \n| Product_id | start_date | end_date   | price | product_id | purchase_date | untis |\n|------------|------------|------------|-------|------------|---------------|-------|\n| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-02-25    | 100   |\n| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-03-01    | 15    |\n| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-02-10    | 200   |\n| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-03-22    | 30    |\n    \n- Then we have to calculate overall number of units sold of each product\n```\nSUM(units)\n```\n- To get an average selling price of each product we then simply divide our numbers like this: \n```\nSUM(u.units * p.price) / SUM(units)::numeric)\n```\n (we cast on of the SUMs to numeric because otherwise result is truncated)\n- We have to remember that there may be some unsold products for which we will get `NULL` so we substitute it for 0 by using `COALESCE()`\n```\n COALESCE(SUM(u.units * p.price) / SUM(units)::numeric, 0)\n```\n- Then we round our value:\n```\nROUND(COALESCE(SUM(u.units * p.price) / SUM(units)::numeric, 0), 2)\n```\n\n\n# Full Solution\n```\n-- Write your PostgreSQL query statement below\nSELECT \n    p.product_id, \n    ROUND(COALESCE(SUM(u.units * p.price) / SUM(units)::numeric, 0), 2) AS average_price\nFROM \n    Prices p LEFT JOIN \n    UnitsSold u ON \n    p.product_id = u.product_id AND \n    u.purchase_date BETWEEN p.start_date AND p.end_date \nGROUP BY \n    p.product_id;\n\n```\n- So that\'s about it. See ya. \n\n
['PostgreSQL']