2025-08-02 13:25:49 +0000 UTC

Average Selling Price
Categories:
Tags:

Code

SELECT 
    Prices.product_id,
    CASE
        WHEN 
            SUM(UnitsSold.units) is null
        THEN 
            0
        ELSE
            ROUND(SUM(UnitsSold.units * Prices.price) / SUM(UnitsSold.units)::numeric, 2)
    END AS average_price
FROM 
    Prices
LEFT JOIN 
    UnitsSold
ON 
    UnitsSold.product_id = Prices.product_id 
    AND UnitsSold.purchase_date 
        BETWEEN Prices.start_date AND Prices.end_date
GROUP BY 
    Prices.product_id;