problemmediumdatabaseleetcode-1070leetcode 1070leetcode1070

Product Sales Analysis III

MediumUpdated: Jul 16, 2025
Practice on:
LeetCode

Problem

Table: Sales

+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Write a solution to select the product id , year , quantity , and price for the first year of every product sold.

Return the resulting table in any order.

The result format is in the following example.

Examples

Example 1

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

Solution

Method 1 – Find First Year Per Product

Intuition

We need to find, for each product, the sales record for its first year. This is a group-by-min and join problem.

Approach

  1. For each product, find the minimum year in the Sales table.
  2. Join back to Sales to get the full record for that year.

Code

MySQL
SELECT s.product_id, s.year AS first_year, s.quantity, s.price
FROM Sales s
JOIN (
    SELECT product_id, MIN(year) AS first_year
    FROM Sales
    GROUP BY product_id
 ) t
ON s.product_id = t.product_id AND s.year = t.first_year;
PostgreSQL
SELECT s.product_id, s.year AS first_year, s.quantity, s.price
FROM Sales s
JOIN (
    SELECT product_id, MIN(year) AS first_year
    FROM Sales
    GROUP BY product_id
 ) t
ON s.product_id = t.product_id AND s.year = t.first_year;
Python (pandas)
# Assume Sales is a pandas DataFrame
import pandas as pd
def product_sales_analysis_iii(Sales: pd.DataFrame) -> pd.DataFrame:
    first_year = Sales.groupby('product_id')['year'].min().reset_index(name='first_year')
    merged = Sales.merge(first_year, left_on=['product_id', 'year'], right_on=['product_id', 'first_year'])
    result = merged[['product_id', 'first_year', 'quantity', 'price']]
    return result

Complexity

  • ⏰ Time complexity: O(N) where N is the number of rows in Sales.
  • 🧺 Space complexity: O(P) where P is the number of products.

Comments