SQL문제풀기

[SQL 문제 풀이] Average Selling Price (평균 판매 가격)

Jun 12, 2025

[SQL 문제 풀이] Average Selling Price (평균 판매 가격)

Contents

내가 작성한 쿼리

Average Selling Price - LeetCode

Can you solve this real interview question? Average Selling Price - 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

Average Selling Price - LeetCode

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

Average Selling Price - LeetCode

notion image

notion image


각 제품의 평균 판매 가격을 구하기 위한 솔루션을 작성합니다.
'average_price'은 소수점 2자리로 반올림해야 합니다.
제품에 판매 단위가 없는 경우 평균 판매 가격은 0으로 가정합니다.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

Oracle


SELECT 
    p.product_id,
    ROUND(NVL(SUM(p.price * u.units) / SUM(u.units), 0), 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;

MySQL


SELECT 
    p.product_id,
    -- IFNULL로 나눌 값이 없을 경우 0 처리
    ROUND(IFNULL(SUM(p.price * u.units) / SUM(u.units), 0), 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;

차이점

NULL처리 함수의 차이

Share article

stupefyee

RSS·Powered by Inblog