LeetCode 1070: Product Sales Analysis III

Problem Restatement

Using the Sales(sale_id, product_id, year, quantity, price) table, find the product_id, first_year, quantity, and price of the first-year sale for each product.

The first year is the earliest year for each product_id.

Input and Output

Return product_id, first_year, quantity, price for each product’s first sale year.

Edge Cases

• Match the aggregate to the question: use raw COUNT or SUM for rows/amounts, and COUNT(DISTINCT ...) only for unique entities.
• Tie cases should return every qualifying row unless the statement asks for a single row.
• For outer joins, keep filters on the joined table in the ON clause when unmatched rows must remain visible.

Common Pitfalls

• Do not put aggregate filters in WHERE; use HAVING after GROUP BY.
• When the answer needs all tied winners, avoid LIMIT 1 unless the statement explicitly asks for one row.

Implementation

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
);

Alternative with Window Function

WITH ranked AS (
    SELECT product_id, year, quantity, price,
           RANK() OVER (PARTITION BY product_id ORDER BY year) AS rk
    FROM Sales
)
SELECT product_id, year AS first_year, quantity, price
FROM ranked
WHERE rk = 1;

Code Explanation

The subquery finds the minimum year per product.

The outer query selects all sales rows that match a product’s first year.

The window function approach uses RANK() partitioned by product and ordered by year to label first-year rows.