[SQL 문제 풀이] Product Sales Analysis III (제품 판매 분석 III)

Stupefyee's avatar
Jul 02, 2025
[SQL 문제 풀이] Product Sales Analysis III (제품 판매 분석 III)
Product Sales Analysis III - LeetCode
Can you solve this real interview question? Product Sales Analysis III - Table: Sales +-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | | price | int | +-------------+-------+ (sale_id, year) is the primary key (combination of columns with unique values) of this table. product_id is a foreign key (reference column) to Product table. Each row records a sale of a product in a given year. A product may have multiple sales entries in the same year. Note that the per-unit price. Write a solution to find all sales that occurred in the first year each product was sold. * For each product_id, identify the earliest year it appears in the Sales table. * Return all sales entries for that product in that year. Return a table with the following columns: product_id, first_year, quantity, and price. Return the result in any order.   Example 1: Input: Sales table: +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | +---------+------------+------+----------+-------+ | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | +---------+------------+------+----------+-------+ Output: +------------+------------+----------+-------+ | product_id | first_year | quantity | price | +------------+------------+----------+-------+ | 100 | 2008 | 10 | 5000 | | 200 | 2011 | 15 | 9000 | +------------+------------+----------+-------+
Product Sales Analysis III - LeetCode
notion image
각 제품이 판매된 첫 해에 발생한 모든 판매를 찾기 위한 해결책을 작성하세요. * 각 product_id에 대해 판매 테이블에 표시되는 가장 이른 연도를 식별하십시오. * 해당 제품의 모든 판매 항목을 해당 연도에 반환합니다. 다음 열이 있는 표를 반환합니다: product_id, first_year, 수량, 그리고 가격. 결과를 순서대로 반환하세요.
 

내가 작성한 쿼리

MySQL, Oracle

-- 각 product_id의 최초 판매 연도를 구함 WITH FirstYear AS ( SELECT product_id, MIN(year) AS first_year FROM Sales GROUP BY product_id ) -- 첫 해의 판매 기록만 JOIN해서 가져옴 SELECT s.product_id, f.first_year, s.quantity, s.price FROM Sales s JOIN FirstYear f ON s.product_id = f.product_id AND s.year = f.first_year;
Share article

stupefyee