Problem Description –
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.
The query result format is in the following example.
Difficulty Level – Easy
Problem Link – Average Selling Price
SELECT product_id, ROUND(SUM(total_price) / SUM(units),2) as average_price FROM ( SELECT p.product_id, p.price, u.units, p.price * u.units as total_price FROM Prices as p join UnitsSold as u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date ) x GROUP BY 1 ORDER BY 1
This is a very simple problem but you have to be careful when joining both the table. If you join the data only on product_id you will get a wrong result. You also have to join the data on dates columns. The purchase date has to be between start and end date.