Average Selling Price

Average Selling Price

문제 내용

아래와 같은 Table 2개가 있다.

product id의 제품이 팔린 날짜의 price와 팔린 units 갯수를 곱하고 unit 하나당 팔린 가격의 평균을 구하는 query를 작성하라

 

접근 방법

1. 두개의 Table을 join 한다

select *
from Prices a,UnitsSold b
where a.product_id = b.product_id

select *
FROM Prices as a
JOIN UnitsSold as b
ON a.product_id=b.product_id

위에 두개는 동일한 inner join이다.

 

2. purchase_date가 start_date와 end_date 사이에 있는 경우만 추출한다.

select *
from Prices a,UnitsSold b
where a.product_id = b.product_id
and b.purchase_date between a.start_date and a.end_date

SELECT *
FROM Prices as a
JOIN UnitsSold as b
ON a.product_id=b.product_id AND (b.purchase_date BETWEEN a.start_date AND a.end_date)

 

3. product_id로 group by 처리 한다. 

하기와 같이 2가지로 개발 가능하다.

select rst.product_id, round(sum(rst.price)/sum(rst.units),2) as average_price
from (
    select a.product_id, a.price * b.units as price, b.units units 
    from Prices a, UnitsSold b
    where a.product_id = b.product_id 
    and b.purchase_date between a.start_date and a.end_date
) rst
group by product_id

SELECT a.product_id,ROUND(SUM(b.units*a.price)/SUM(b.units),2) as average_price
FROM Prices as a
JOIN UnitsSold as b
ON a.product_id=b.product_id AND (b.purchase_date BETWEEN a.start_date AND a.end_date)
GROUP BY product_id;

 

오랫만에 sql 하는것도 재미있네 종종 풀어야 겠다.