Product Sales Analysis III LeetCode Solution

Last updated on January 21st, 2025 at 10:00 pm

Here, we see the Product Sales Analysis III LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Medium

Product Sales Analysis III LeetCode Solution

Product Sales Analysis III LeetCode Solution

1. Problem Statement

Column NameType
sale_idint
product_idint
yearint
quantityint
priceint
Table: Sales

(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.

Column NameType
product_idint
product_namevarchar
Table: Product

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 idyearquantity, 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.

Example 1:
Input:

sale_id product_id year quantity price
11002008105000
21002009125000
72002011159000
Sales table:
product_id product_name
100Nokia
200Apple
300Samsung
Product table:

Output:

product_id first_year quantity price
1002008105000
2002011159000

2. Code Implementation in Different Languages

2.1 Product Sales Analysis III MySQL

select 
  product_id, 
  year as first_year, 
  quantity, 
  price 
from 
  Sales 
where 
  (product_id, year) in (
    select 
      product_id, 
      min(year) as year 
    from 
      Sales 
    group by 
      product_id
  );

2.2 Product Sales Analysis III Pandas

import pandas as pd

def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
  df = sales.groupby('product_id', as_index=False)['year'].min()
  return sales.merge(df, on='product_id', how='inner')\
    .query('year_x == year_y')\
    .rename(columns={'year_x': 'first_year'})\
    [['product_id', 'first_year', 'quantity', 'price']]
Scroll to Top