First-Year Product Sales Snapshot

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

sales =

[{"sale_id":1,"product_id":100,"year":2018,"quantity":10,"price":5000},{"sale_id":2,"product_id":100,"year":2019,"quantity":12,"price":5200},{"sale_id":3,"product_id":200,"year":2020,"quantity":7,"price":9000}]

First-Year Product Sales Snapshot

Hints

First-Year Product Sales Snapshot

Hints