Weighted Average Selling Price in SQL

Question

Given `prices` (product_id, start_date, end_date, price) and `units_sold` (product_id, purchase_date, units), calculate the average selling price for each product rounded to 2 decimal places. If a product has no sales, return 0.

Accepted Answer

Optimal Solution:
SELECT 
  p.product_id,
  COALESCE(
    ROUND(SUM(u.units * p.price)::numeric / NULLIF(SUM(u.units), 0), 2),
    0
  ) AS average_price
FROM prices p
LEFT JOIN units_sold u 
  ON p.product_id = u.product_id 
 AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id
ORDER BY p.product_id ASC;

Technical Explanation:
A LEFT JOIN connects prices to sales during the active date range. `SUM(units * price) / SUM(units)` gives the weighted average. `NULLIF(SUM(units), 0)` prevents division by zero.

product_id	price	units	purchase_date	start_date	end_date
1	5	100	2026-02-25	2026-02-20	2026-02-28
1	20	15	2026-03-05	2026-03-01	2026-03-10