[SQL50] 1251. Average Selling Price

난이도: EASY

 

문제 링크: https://leetcode.com/problems/average-selling-price/description/?envType=study-plan-v2&envId=top-sql-50

문제

Table: Prices
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.  

Table: UnitsSold
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the date, units, and product_id of each product sold. 

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.
The result format is in the following example.

Example 1:
Input: 
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+

UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+

Output: 
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+

진짜
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

---

 

시도 1

SELECT DISTINCT p.product_id, ROUND(AVG(price), 2) AS average_price  
FROM Prices p JOIN UnitsSold u 
    ON p.product_id = u.product_id  
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id

사실 저 AVG(price) 는 아닌건 알았는데, 걍 뭘어케해야할지 모르겠어서 그냥 넣음....ㅋㅋㅋㅋㅋㅋ

챗지야 도와줘.... 수학에 약한 편(?)

---

 

나의 최종 제출 답안:

SELECT 
    DISTINCT p.product_id, 
    ROUND(
         CASE 
           WHEN SUM(u.units) IS NULL OR SUM(u.units) = 0 THEN 0
           ELSE SUM(u.units * p.price) / SUM(u.units)
         END, 
       2) AS average_price  
FROM Prices p LEFT JOIN UnitsSold u 
    ON p.product_id = u.product_id  
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id

 

 

---

 

chatGPT에게 함수 간단히 설명 요구

 

SUM 함수

• 정의: Oracle의 집계 함수(Aggregate Function)로, 특정 컬럼(또는 표현식)의 값을 합산합니다.
• 사용 예시: 이 쿼리는 employees 테이블에서 모든 직원의 salary를 합산합니다.

SELECT SUM(salary) FROM employees;

 

ROUND 함수

• 정의: 숫자(Number)나 날짜(Date)를 지정된 자릿수(또는 단위)로 반올림하는 함수입니다.
• 숫자에 대한 예시:
  결과: 123.46 (소수점 둘째 자리까지 반올림)

SELECT ROUND(123.456, 2) AS rounded_value FROM dual;

• 날짜에 대한 예시:
  결과: 2023-03-01 (‘MONTH’ 기준으로 날짜를 반올림)

SELECT ROUND(DATE '2023-03-10', 'MONTH') AS rounded_date FROM dual;

 

Oracle에서 ROUND(날짜, 'MONTH')는 해당 날짜를 “가장 가까운 달의 1일 00시 00분”으로 반올림합니다.

• 1일부터 15일 사이면, 그 달의 1일로 내림.
• 16일부터 말일 사이면, 다음 달 1일로 올림.

예를 들어,

SELECT ROUND(DATE '2023-03-10', 'MONTH') AS rounded_date FROM dual;

• 3월 10일은 1일부터 15일 사이이므로, 2023-03-01 00:00:00으로 반올림됩니다.
• 실제 출력 형식은 Oracle의 NLS_DATE_FORMAT 설정에 따라 달라질 수 있지만, 논리적으로는 “2023-03-01”로 해석됩니다.