# 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

| Table | Schema |
|---|---|
| `Sales` | `sale_id INT`, `product_id INT`, `year INT`, `quantity INT`, `price INT` |

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

```sql
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

```sql
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.