Product Sales Analysis III - Problem

You are given a Sales table containing sales records for products over different years.

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

The primary key is (sale_id, year). Each row records a sale of a product in a given year. Note that the price is the per-unit price.

Task: Write a solution to find all sales that occurred in the first year each product was sold.

For each product_id, identify the earliest year it appears in the Sales table, then return all sales entries for that product in that year.

Return a table with the following columns: product_id, first_year, quantity, and price.

Table Schema

Sales
Column Name Type Description
sale_id PK int Sale identifier
product_id int Product identifier
year PK int Year of the sale
quantity int Quantity sold
price int Per-unit price
Primary Key: (sale_id, year)
Note: A product may have multiple sales entries in the same year

Input & Output

Example 1 — Multiple Products with Different First Years
Input Table:
sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2009 12 5000
7 200 2011 15 9000
Output:
product_id first_year quantity price
100 2008 10 5000
200 2011 15 9000
💡 Note:

Product 100 first appeared in 2008, so we return its 2008 sale. Product 200 first appeared in 2011, so we return its 2011 sale. The 2009 sale of product 100 is excluded since it's not the first year.
Example 2 — Multiple Sales in First Year
Input Table:
sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2008 8 4500
3 100 2009 12 5000
Output:
product_id first_year quantity price
100 2008 10 5000
100 2008 8 4500
💡 Note:

Product 100 first appeared in 2008 with multiple sales. Both 2008 sales are included in the result, while the 2009 sale is excluded since it's not from the first year.
Example 3 — Single Product Single Year
Input Table:
sale_id product_id year quantity price
1 300 2020 5 2000
Output:
product_id first_year quantity price
300 2020 5 2000
💡 Note:

Product 300 has only one sale record in 2020, which is automatically its first year, so this sale is returned.

Constraints

  • 1 ≤ sale_id ≤ 10000
  • 1 ≤ product_id ≤ 1000
  • 2000 ≤ year ≤ 2025
  • 1 ≤ quantity ≤ 1000
  • 1 ≤ price ≤ 100000

Visualization

Tap to expand
Product Sales Analysis III - First Year SalesInput: All Sales Dataproduct_idyearquantityprice100200810500010020091250002002011159000FILTERKeep only first year salesOutput: First Year Sales Onlyproduct_idfirst_yearquantityprice10020081050002002011159000✓ First year sales (kept)✗ Later year sales (filtered out)
Understanding the Visualization
1
Input
Sales table with multiple years per product
2
Find First Year
Identify minimum year per product
3
Filter
Return only first year sales
Key Takeaway
🎯 Key Insight: Use window functions to efficiently identify minimum values per group without complex subqueries

Related Problems

Asked in
Amazon 15 Google 12 Microsoft 8 Meta 6
23.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen