This Leetcode problem Product Sales Analysis III LeetCode Solution is done in SQL.

List of all LeetCode Solution

Product Sales Analysis III LeetCode Solution

Problem Statement

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.

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.

Example 1:
Input:

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

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

Product Sales Analysis III LeetCode Solution 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
  );Code language: SQL (Structured Query Language) (sql)

Product Sales Analysis III LeetCode Solution 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']]Code language: SQL (Structured Query Language) (sql)