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 INPUT: Sales Table sale_id product_id year qty price 1 100 2008 10 5000 2 100 2009 12 5500 3 100 2008 15 5200 4 200 2011 20 3000 5 200 2012 25 3200 6 300 2014 8 8000 Goal: Find all sales from the FIRST YEAR each product was sold ALGORITHM STEPS 1 Subquery: Find First Year GROUP BY product_id SELECT MIN(year) product_id | first_year 100 | 2008 200 | 2011 2 Join with Sales Match product_id AND year 3 Filter First Year Sales Keep only matching rows 4 Select Columns product_id, first_year, quantity, price SELECT s.product_id, s.year AS first_year, s.quantity, s.price FROM Sales s JOIN (...) FINAL RESULT product_id first_year qty price 100 2008 10 5000 100 2008 15 5200 200 2011 20 3000 300 2014 8 8000 Product 100: 2 sales in 2008 (first year) - INCLUDED Product 100: 2009 sale - EXCLUDED (not first year) STATUS: OK 4 rows returned Product 100: 2008 (first) Product 200: 2011 (first) Product 300: 2014 (first) Key Insight: Use a subquery with GROUP BY and MIN(year) to find each product's first sale year. Then JOIN this result back to the Sales table to get ALL sales from that first year. This handles multiple sales per product in the same year correctly, returning all of them. TutorialsPoint - Product Sales Analysis III | Optimal Solution (Subquery + JOIN)

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