SQL Interview Questions – Average Selling Price

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

Solution –

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.