Last updated on October 9th, 2024 at 10:35 pm

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

List of all LeetCode Solution

Sales Analysis III LeetCode Solution

Problem Statement

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.

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:

Output:

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.

1. Sales Analysis III LeetCode Solution 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. Sales Analysis III LeetCode Solution 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']]