1251.Average Selling Price

* Problem

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Return the result table in any order.

Explanation: 
Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

 

* 1st try(Wrong)

 

select   a.product_id,               round(sum(a.price * b.units) * 1.0/sum(b.units),2) average_price     from Prices a      left join UnitsSold b        on(a.product_id = b.product_id)       and (b.purchase_date between a.start_date and a.end_date)     group by 1;

 

null 값에 대한 고려가 안되어 test case 에서 걸렸다. (* 항상 케이스 고려 명심할것!!)

 

* Final Solution (Success)

select a.product_id,            ifnull(round(sum(a.price * b.units) * 1.0/sum(b.units),2),0) average_price     from Prices a      left join UnitsSold b        on(a.product_id = b.product_id)       and (b.purchase_date between a.start_date and a.end_date)     group by 1;