[LeetCode] 1070. Product Sales Analysis III - SQL

링크: https://leetcode.com/problems/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) toProduct table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order.

The result format is in the following example.

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  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

---

문제풀이

목표: 각각의 product 가 팔린 첫 번째 해에 대해서 product_id, year, quantity, 그리고 price 를 조회하라.

최종코드

SELECT 
    product_id, 
    year as first_year, 
    quantity, 
    price
FROM Sales
WHERE (product_id, year) IN (
    SELECT product_id, MIN(year) 
    FROM Sales
    GROUP BY product_id
)

설명

WHERE 절

WHERE (product_id, year) IN (
    SELECT product_id, MIN(year) 
    FROM Sales
    GROUP BY product_id

• WHERE 절에서 서브쿼리를 이용해 서브쿼리에 해당하는 product_id, year 를 불러온다.
• 서브쿼리:
  • Sales 테이블에서
  • product_id 를 기준으로 그룹화 하고,
  • product_id와 MIN 을 사용해 제품이 처음 팔린 year 를 구한다.

SELECT 절

SELECT 
    product_id, 
    year as first_year, 
    quantity, 
    price

• 나머지는 문제의 지시사항에 따라 조회해주면 된다.