1068. Product Sales Analysis I

Problem Statement

Table: Sales

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.

Note that the price is per unit.

Table: Product

Column Name	Type
product_id	int
product_name	varchar

product_id is the primary key of this table.

Each row of this table indicates the product name of each product.

Instructions

• Write a solution to report the product_name, year, and price for each sale_id in the Sales table.
• Return the resulting table in any order.
• The result format is in the following example.

Example

Input:

Sales table

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

Product table

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output:

product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

Explanation:

From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008.

From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009.

From sale_id = 7, we can conclude that Apple was sold for 9000 in the year 2011.

Submissions

PostgreSQL

SELECT p.product_name, s.year, s.price
FROM Sales s
JOIN Product p ON s.product_id = p.product_id

MySQL

SELECT p.product_name, s.year, s.price
FROM Sales s
JOIN Product p ON s.product_id = p.product_id

MS SQL Server

SELECT p.product_name, s.year, s.price
FROM Sales s
JOIN Product p ON s.product_id = p.product_id

Pandas

import pandas as pd

def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    merged_df = pd.merge(sales, product, on='product_id')
    result_df = merged_df[['product_name', 'year', 'price']]

    return result_df

Explanations

PostgreSQL, MySQL, & MS SQL Server

Submitted by @noeyislearning

• SELECT p.product_name, s.year, s.price: This query selects the product_name, year, and price columns from the Product and Sales tables.
• FROM Sales s: This part of the statement specifies the table from which you want to retrieve the data. Here, Sales is the table, and it's aliased as s.
• JOIN Product p ON s.product_id = p.product_id: This clause joins the Sales and Product tables on the product_id column, linking the two tables based on this common column.

Pandas

Submitted by @noeyislearning