First-Year Product Sales Snapshot

Medium

You are given a Sales table where each row is a recorded sale event: sale_id: unique sale event identifier product_id: product identifier year: calendar year of the sale event quantity: number of units sold in that event price: per-unit sale price captured for that event A product can appear in multiple years, and it may also have multiple sale rows in the same year. Task: For each product_id, determine the earliest year in which it appears. Return every sale row for that product from that earliest year. Return output with exactly these columns: product_id first_year quantity price Result row order may be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 1       | 100        | 2018 | 10       | 5000  |
| 2       | 100        | 2019 | 12       | 5200  |
| 3       | 200        | 2020 | 7        | 9000  |

Output

[
  {"product_id":100,"first_year":2018,"quantity":10,"price":5000},
  {"product_id":200,"first_year":2020,"quantity":7,"price":9000}
]

Explanation

Product 100 first appears in 2018, so only its 2018 row is returned. Product 200 appears first in 2020, so its 2020 row is returned.

Example 2

Input

Sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 11      | 300        | 2017 | 3        | 100   |
| 12      | 300        | 2017 | 8        | 95    |
| 13      | 300        | 2018 | 6        | 105   |

Output

[
  {"product_id":300,"first_year":2017,"quantity":3,"price":100},
  {"product_id":300,"first_year":2017,"quantity":8,"price":95}
]

Explanation

The earliest year for product 300 is 2017, and all rows in that year are included.

Example 3

Input

Sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 20      | 401        | 2022 | 2        | 700   |
| 21      | 402        | 2021 | 5        | 650   |
| 22      | 401        | 2021 | 4        | 680   |
| 23      | 402        | 2021 | 1        | 640   |
| 24      | 401        | 2021 | 3        | 675   |

Output

[
  {"product_id":401,"first_year":2021,"quantity":4,"price":680},
  {"product_id":401,"first_year":2021,"quantity":3,"price":675},
  {"product_id":402,"first_year":2021,"quantity":5,"price":650},
  {"product_id":402,"first_year":2021,"quantity":1,"price":640}
]

Explanation

Both products have earliest year 2021, and each qualifying row in 2021 must be returned.

Constraints

1 <= number of rows in Sales <= 10,000
1 <= sale_id <= 1,000,000,000
1 <= product_id <= 1,000,000,000
1970 <= year <= 2100
1 <= quantity <= 1,000,000
1 <= price <= 1,000,000,000
A product can have multiple rows in the same year
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: product_id, first_year, quantity, price

Sales: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 1 | 100 | 2018 | 10 | 5000 | | 2 | 100 | 2019 | 12 | 5200 | | 3 | 200 | 2020 | 7 | 9000 |

Sales: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 11 | 300 | 2017 | 3 | 100 | | 12 | 300 | 2017 | 8 | 95 | | 13 | 300 | 2018 | 6 | 105 |

Sales: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 20 | 401 | 2022 | 2 | 700 | | 21 | 402 | 2021 | 5 | 650 | | 22 | 401 | 2021 | 4 | 680 | | 23 | 402 | 2021 | 1 | 640 | | 24 | 401 | 2021 | 3 | 675 |

[ {"product_id":401,"first_year":2021,"quantity":4,"price":680}, {"product_id":401,"first_year":2021,"quantity":3,"price":675}, {"product_id":402,"first_year":2021,"quantity":5,"price":650}, {"product_id":402,"first_year":2021,"quantity":1,"price":640} ]