[SQL풀이][LeetCode][easy] 1251. Average Selling Price

https://leetcode.com/problems/average-selling-price/description/

Average Selling Price - LeetCode

 

문제설명

Prices와 UnitsSold 테이블을 참고하여 상품ID별 평균 판매가격을 계산하라.

 

- Prices: 상품 ID와 기간별 가격
- UnitsSold: 상품 ID, 판매일자 및 판매량

 

 

접근방법
1. Prices 테이블과 UnitsSold 테이블을 join하여 각 판매일에 해당하는 판매금액(revenue)을 계산한다.

2. 각 상품별 판매금액의 합계와 판매량을 이용하여 평균 판매가격을 계산한다.

 

 

풀이코드

-- 각 판매일에 해당하는 price 정보를 join한다. 
WITH RevenueSold AS (
    SELECT 
        p.product_id,
        p.price,
        us.units,
        p.price * us.units as revenue
    FROM 
        Prices AS p, 
        UnitsSold AS us
    WHERE 
        p.product_id = us.product_id AND
        us.purchase_date BETWEEN p.start_date AND p.end_date 
)

-- 각 판매일에 해당하는 판매금액을 계산한다.
AvgSellingPrices AS(
    SELECT 
        product_id,
        ROUND(SUM(revenue) / SUM(units), 2) AS 'average_price'
    FROM RevenueSold
    GROUP BY product_id
)

-- 각 상품별 판매금액의 합계와 판매량을 이용하여 평균 판매가격을 계산한다.
SELECT 
    DISTINCT product_id,
    IFNULL(asp.average_price, 0) AS average_price
FROM Prices AS p
LEFT JOIN AvgSellingPrices AS asp
USING (product_id)

 

 

추가 설명

CTE

- RevenueSold : 두 테이블을 JOIN한 결과를 저장하는 테이블. 판매일자 별 판매금액(revenue) 컬럼 생성.
- AvgSellingPrices : GROUP BY 를 사용하여 각 상품별 평균 판매가격을 계산한 결과를 저장하는 테이블.

 

Main Query

- test case 中 Prices 테이블에 가격정보가 있지만 UnitsSold 테이블에 판매기록이 없는 product_id가 존재함. 

- 문제에서, 판매이력이 없는 product_id의 평균 판매가격은 0으로 출력을 요구하기 때문에, Prices 테이블 product_id에 AvgSellingPrices에서 구한 값을 join 하는 방식으로 문제 해결.

 

 

개선 방법

- 범주로 주어진 date(start_date, end_date)와 단일 date(purchase_date)를 JOIN하는 방법이 이 문제의 핵심

- 제한 시간내에 문제를 풀기위하여 CTE를 이용하여 단계별로 문제풀이 진행

- RevenueSold테이블의 WHERE절을 JOIN 뒤 ON절에 똑같이 적용하여 더욱 간단한 코드로 풀이 가능

SELECT 
    p.product_id, 
    IFNULL(ROUND(SUM(units*price)/SUM(units),2),0) AS average_price
FROM Prices AS p 
LEFT JOIN UnitsSold AS us
ON 
    p.product_id = us.product_id AND
    us.purchase_date BETWEEN start_date AND end_date
GROUP BY product_id