Last updated on March 10th, 2025 at 10:54 pm

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

List of all LeetCode Solution

Level of Question

Easy

Sales Analysis III LeetCode Solution

1. Problem Statement

Column Name	Type
product_id	int
product_name	varchar
unit_price	int

product_id is the primary key (column with unique values) of this table. Each row of this table indicates the name and the price of each product.

Column Name	Type
seller_id	int
product_id	int
buyer_id	int
sale_date	date
quantity	int
price	int

This table can have duplicate rows. product_id is a foreign key (reference column) to the Product table. Each row of this table contains some information about one sale.

Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

Return the result table in any order.

The result format is in the following example.

Example 1:
Input:

product_id	product_name	unit_price
1	S8	1000
2	G4	800
3	iPhone	1400

seller_id	product_id	buyer_id	sale_date	quantity	price
1	1	1	2019-01-21	2	2000
1	2	2	2019-02-17	1	800
2	2	3	2019-06-02	1	800
3	3	4	2019-05-13	2	2800

Output:

product_id	product_name
1	S8

Explanation:
The product with id 1 was only sold in the spring of 2019.
The product with id 2 was sold in the spring of 2019 but was also sold after the spring of 2019.
The product with id 3 was sold after spring 2019. We return only product 1 as it is the product that was only sold in the spring of 2019.

2. Code Implementation in Different Languages

2.1 Sales Analysis III MySQL

select 
  product_id, 
  product_name 
from 
  Sales 
  inner join product using(product_id) 
group by 
  product_id 
having 
  sum(
    if(
      sale_date between '2019-01-01' 
      and '2019-03-31', 
      1, 
      0
    )
  ) = sum(
    if(sale_date, 1, 0)
  );

2.2 Sales Analysis III Pandas

import pandas as pd

def sales_analysis(product: pd.DataFrame, sales: pd.DataFrame) -> pd.DataFrame:
    start_time = pd.to_datetime('2019-01-01')
    end_time = pd.to_datetime('2019-03-31')
    df = sales.groupby('product_id').filter(lambda x:
        min(x['sale_date']) >= start_time and max(x['sale_date']) <= end_time
    )
    df = df.drop_duplicates(subset = 'product_id')
    df = df.merge(product, left_on = 'product_id', right_on = 'product_id')
    return df[['product_id', 'product_name']]