SQL MOSAIC #1251. Average Selling Price
Jul 5, 2022
Date: 7/5/2022
Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places. Return the result table in any order.
Thinking Pathway:
1. Create the subquery first.
2. Datediff or just compare the date
Solution(s):
SELECT product_id, ROUND(total/total_units,2) AS average_price
FROM(
SELECT p.product_id, SUM(u.units) AS total_units, SUM(price*units) AS total
FROM Prices p JOIN UnitsSold u ON p.product_id = u.product_id
WHERE u.purchase_date — p.start_date >= 0 AND u.purchase_date — p.end_date <= 0
GROUP BY product_id
) as T
