Last updated on October 10th, 2024 at 12:04 am
This Leetcode problem Product Sales Analysis III LeetCode Solution is done in SQL.
List of all LeetCode Solution
Level of Question
Medium
Product Sales Analysis III LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
sale_id | int |
product_id | int |
year | int |
quantity | int |
price | int |
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 Name | Type |
product_id | int |
product_name | varchar |
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 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 |
1. 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 );
2. 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']]