Leetcode SQL 50 - 1070. Product Sales Analysis III

문제 사이트: @leetcode

 

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.

 

<나의 정답 쿼리>

-- Write your PostgreSQL query statement below
WITH A2 AS
(
    SELECT
         product_id
        ,MIN(year) AS first_year
    FROM Sales
    GROUP BY 
         product_id
)
SELECT
     A1.product_id AS product_id
    ,A1.year AS first_year
    ,A1.quantity
    ,A1.price
FROM Sales A1
INNER JOIN A2
    ON  A1.product_id = A2.product_id
    AND A1.year = A2.first_year

 

내가 생각한 문제 풀이 순서

1. product_id 별로 출시 일자 뽑기 (나중에 product_id와 출시일자를 조건에 줘서 원하는 값만 가져오려고)
2. 1번에서 뽑은 product_id와 출시 일자를 키 값으로 하여 INNER JOIN으로 원하는 값만 가져오기

임시테이블로 1번을 만든 이유는 서브 쿼리 내에서 FROM을 2번 하기 싫어서.. CTE 사용