[LeetCode] Average Selling Price

date

Jul 26, 2023

slug

leetcode1251

author

status

Public

[문제 링크]

LeetCode - The World's Leading Online Programming Learning Platform

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

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

[풀이 코드]

1. JOIN


SELECT *
FROM Prices P JOIN UnitsSold U ON P.product_id = U.product_id


| product_id | start_date | end_date   | price | product_id | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ---------- | ------------- | ----- |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-02-25    | 100   |
| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-03-01    | 15    |
| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-03-01    | 15    |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-02-10    | 200   |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-02-10    | 200   |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-03-22    | 30    |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-03-22    | 30    |

2. Price * Units


SELECT P.product_id, price, units, price * units AS total_price
FROM Prices P JOIN UnitsSold U ON P.product_id = U.product_id


| product_id | price | units | total_price |
| ---------- | ----- | ----- | ----------- |
| 1          | 20    | 100   | 2000        |
| 1          | 5     | 100   | 500         |
| 1          | 20    | 15    | 300         |
| 1          | 5     | 15    | 75          |
| 2          | 30    | 200   | 6000        |
| 2          | 15    | 200   | 3000        |
| 2          | 30    | 30    | 900         |
| 2          | 15    | 30    | 450         |

3. SUM(Price * Units), GROUP BY


SELECT P.product_id, price, units, SUM(price * units) AS total_price
FROM Prices P JOIN UnitsSold U ON P.product_id = U.product_id
GROUP BY P.product_id


| product_id | price | units | total_price |
| ---------- | ----- | ----- | ----------- |
| 1          | 20    | 100   | 2875        |
| 2          | 30    | 200   | 10350       |

4. SELECT, AVERAGE


# 오답

SELECT 
  P.product_id, 
  ROUND(SUM(P.price * U.units)/SUM(U.units),2) 
  AS average_price
FROM Prices P JOIN UnitsSold U 
    ON P.product_id = U.product_id 
GROUP BY P.product_id


| product_id | total_price |
| ---------- | ----------- |
| 1          | 12.5        |
| 2          | 22.5        |

이게 왜 틀렸지?….

각 제품의 평균 판매 가격을 구하는 SQL 쿼리를 작성합니다. 평균_가격은 소수점 둘째 자리에서 반올림해야 합니다.

이것만 읽었는데 이런 조건이 있었다.

🔍

이 테이블의 각 행은 start_date부터 end_date까지의 기간에 해당하는 product_id의 가격을 나타냅니다.

각 product_id에 대해 겹치는 기간은 두 개가 없습니다. 즉, 동일한 product_id에 대해 교차하는 기간이 두 개 없을 것입니다.

생각해 보니 price는 start_date~end_date가 있고

units는 purchase_date만 있어서 조인을 하면 날짜가 중복되어서 출력이 되겠구나………

어쩐지 중복으로 두 개씩 나온다 했다….


# 정답

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

한번에 코드를 작성하기보다는

하나하나 실행을 확인해 가며 쿼리를 짜야겠다.