PostreSQL solution with good explanation
Approach\n Describe your approach to solving the problem. \n\nSince some products may not be sold, we should select from Prices table with LEFT JOIN (to keep th
# Approach\n<!-- Describe your approach to solving the problem. -->\n\nSince some products may not be sold, we should select from Prices table with **LEFT JOIN** (to keep them in a result table). For such products there will be null in average_price column, to avoid that I used **CASE** clause to catch such cases and display 0.\n\nTo get specific price on the date of purchaise we should use **BETWEEN** clause to match rows.\n\n**Why did i use here cast?**\n\nSince **SUM** of integer units returns in postgresql integer (whole number), we should cast one of the values to "numeric" type (numeric type is a decimal in postresql to store precise values like money).\n\n**Why not float?**\nBecause **ROUND** doesn\'t work with float type in PostgreSQL.\n\n\n# Complexity \n![image.png](https://assets.leetcode.com/users/images/bd8b3e69-4790-4141-956f-507951af1752_1730449785.3143888.png)\n\n\n# Code\n```postgresql []\n-- Write your PostgreSQL query statement below\n\nSELECT \n    p.product_id,\n    CASE\n        WHEN SUM(u.units) is null\n        THEN 0\n        ELSE\n            ROUND(SUM(u.units * p.price) / SUM(u.units)::numeric, 2)\n    END\n    AS average_price\nFROM \n    Prices p\nLEFT JOIN \n    UnitsSold u\nON \n    u.product_id = p.product_id \n    AND u.purchase_date BETWEEN p.start_date AND p.end_date\nGROUP BY \n    p.product_id;\n\n\n```\n![image.png](https://assets.leetcode.com/users/images/91ebf582-2dc8-4605-870d-02c41b27c552_1730450338.9180543.png)\n\n\n
['PostgreSQL']