Product Sales Analysis I — LeetCode 1068 Python Solution

The problem

For every sale, report the name of the product that was sold, the year of the sale and the unit price, in any order. The Sales table has one row per sale: sale_id (int), product_id (int), year (int), quantity (int) and price (int, the price per unit), with the pair (sale_id, year) as the primary key. The Product table maps product_id (int, the primary key) to product_name (varchar).

Example

Sales table:

| sale_id | product_id | year | quantity | price |
| ------- | ---------- | ---- | -------- | ----- |
| 11      | 300        | 2019 | 25       | 1200  |
| 12      | 300        | 2020 | 30       | 1150  |
| 13      | 400        | 2020 | 8        | 4300  |

Product table:

| product_id | product_name |
| ---------- | ------------ |
| 300        | Router       |
| 400        | Laptop       |

Result:

| product_name | year | price |
| ------------ | ---- | ----- |
| Router       | 2019 | 1200  |
| Router       | 2020 | 1150  |
| Laptop       | 2020 | 4300  |

Each sale is matched to its product name through product_id, so the two Router sales keep their own year and price and the single Laptop sale follows.

Python solution

import pandas as pd

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

Complexity

Related problems