LeetCode 코딩 테스트 - Product Sales Analysis III(LV.Medium)

 

 

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 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_id, year
# 쿼리 계산 방법 : 1. group by min으로 상품 별 첫 해의 정보 가져오기 -> 2. cte를 조건문에 사용해 상품별 첫해 정보 추출 
# 데이터의 기간 : x 
# 사용할 테이블 : sales
# JOIN KEY : x
# 데이터 특징 : x
# 1
WITH first_year AS (
    SELECT
        product_id,
        MIN(year) AS first_year
    FROM sales
    GROUP BY
        product_id
)
SELECT
    product_id,
    year AS first_year,
    quantity,
    price
FROM sales
# 2
WHERE
    (product_id, year) IN (SELECT product_id, first_year FROM first_year)