Product Sales Analysis II — LeetCode 1069 Python Solution

The problem

For every product, report its id and the total quantity sold across all years, 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_id | total_quantity |
| ---------- | -------------- |
| 300        | 55             |
| 400        | 8              |

Product 300 sold 25 units in 2019 and 30 in 2020 for a total of 55, and product 400 sold 8 units in a single year.

Python solution

import pandas as pd

def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    res = sales.groupby('product_id', as_index=False)['quantity'].sum()
    res.columns = ['product_id', 'total_quantity']
    return res

Complexity

Related problems