Total sales amount by year

# Table: Product
# 
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | product_id    | int     |
# | product_name  | varchar |
# +---------------+---------+
# product_id is the primary key (column with unique values) for this table.
# product_name is the name of the product.
# 
#  
# 
# Table: Sales
# 
# +---------------------+---------+
# | Column Name         | Type    |
# +---------------------+---------+
# | product_id          | int     |
# | period_start        | date    |
# | period_end          | date    |
# | average_daily_sales | int     |
# +---------------------+---------+
# product_id is the primary key (column with unique values) for this table.
# period_start and period_end indicate the start and end date for the sales period, and both dates are inclusive.
# The average_daily_sales column holds the average daily sales amount of the items for the period.
# The dates of the sales years are between 2018 to 2020.
# 
#  
# 
# Write a solution to report the total sales amount of each item for each year, with corresponding product_name, product_id, report_year, and total_amount.
# 
# Return the result table ordered by product_id and report_year.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Product table:
# +------------+--------------+
# | product_id | product_name |
# +------------+--------------+
# | 1          | LC Phone     |
# | 2          | LC T-Shirt   |
# | 3          | LC Keychain  |
# +------------+--------------+
# Sales table:
# +------------+--------------+-------------+---------------------+
# | product_id | period_start | period_end  | average_daily_sales |
# +------------+--------------+-------------+---------------------+
# | 1          | 2019-01-25   | 2019-02-28  | 100                 |
# | 2          | 2018-12-01   | 2020-01-01  | 10                  |
# | 3          | 2019-12-01   | 2020-01-31  | 1                   |
# +------------+--------------+-------------+---------------------+
# Output:
# +------------+--------------+-------------+--------------+
# | product_id | product_name | report_year | total_amount |
# +------------+--------------+-------------+--------------+
# | 1          | LC Phone     |    2019     | 3500         |
# | 2          | LC T-Shirt   |    2018     | 310          |
# | 2          | LC T-Shirt   |    2019     | 3650         |
# | 2          | LC T-Shirt   |    2020     | 10           |
# | 3          | LC Keychain  |    2019     | 31           |
# | 3          | LC Keychain  |    2020     | 31           |
# +------------+--------------+-------------+--------------+
# Explanation:
# LC Phone was sold for the period of 2019-01-25 to 2019-02-28, and there are 35 days for this period. Total amount 35*100 = 3500.
# LC T-shirt was sold for the period of 2018-12-01 to 2020-01-01, and there are 31, 365, 1 days for years 2018, 2019 and 2020 respectively.
# LC Keychain was sold for the period of 2019-12-01 to 2020-01-31, and there are 31, 31 days for years 2019 and 2020 respectively.

import pandas as pd

def total_sales(product: pd.DataFrame, sales: pd.DataFrame) -> pd.DataFrame:
  rows = []
  for _, row in sales.iterrows():
    start = pd.Timestamp(row['period_start'])
    end = pd.Timestamp(row['period_end'])
    for year in range(start.year, end.year + 1):
      year_start = max(start, pd.Timestamp(f'{year}-01-01'))
      year_end = min(end, pd.Timestamp(f'{year}-12-31'))
      days = (year_end - year_start).days + 1
      rows.append({
        'product_id': row['product_id'],
        'report_year': str(year),
        'total_amount': days * row['average_daily_sales']
      })
  result = pd.DataFrame(rows)
  result = result.merge(product, on='product_id')
  return result[['product_id', 'product_name', 'report_year', 'total_amount']].sort_values(['product_id', 'report_year'])