[leetcode]1070. Product Sales Analysis III

문제

https://leetcode.com/problems/product-sales-analysis-iii/description/?source=submission-noac

코드

SELECT
    product_id,
    year AS first_year,
    quantity,
    price
FROM
    Sales
WHERE 1=1
    AND (product_id, year) IN (SELECT product_id, MIN(year) AS year
                               FROM Sales
                               GROUP BY product_id)

후기

처음에는 아래와 같은 코드로 했는데 안됨..;;

SELECT
    product_id,
    MIN(year) AS first_year,
    quantity,
    price
FROM
    Sales
GROUP BY
    product_id

확인해 보니 product_id와 first_year가 그룹화 되어서 출력 되어야 하는데
위에 코드처럼 하려면 first_year를 그룹화 할 수가 없음.

그렇다면 WITH 구문으로도 할 수 있을 듯!

WITH min_year AS(
    SELECT
        product_id,
        MIN(year) AS first_year
    FROM
        Sales
    GROUP BY
        product_id
)
SELECT
    m.product_id,
    m.first_year,
    s.quantity,
    s.price
FROM
    Sales s
    INNER JOIN min_year m
        ON s.product_id=m.product_id
            AND s.year=m.first_year