Total Sales Amount by Year - Problem

Given two tables Product and Sales, write a SQL query to report the total sales amount of each item for each year.

The Product table contains basic product information, while the Sales table contains sales periods with average daily sales amounts. Each sales period can span multiple years (2018-2020), so you need to calculate how much was sold in each individual year.

Return the result with columns: product_name, product_id, report_year, and total_amount.

Note: Sales periods are inclusive of both start and end dates. If a period spans multiple years, split the sales proportionally by the number of days in each year.

Table Schema

Product
Column Name Type Description
product_id PK int Primary key, unique identifier for each product
product_name varchar Name of the product
Primary Key: product_id
Sales
Column Name Type Description
product_id PK int Foreign key referencing Product table
period_start date Start date of sales period (inclusive)
period_end date End date of sales period (inclusive)
average_daily_sales int Average daily sales amount during the period
Primary Key: product_id

Input & Output

Example 1 — Multi-year Sales Period
Input Tables:
Product
product_id product_name
1 LC Phone
2 LC T-Shirt
3 LC Keychain
Sales
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_name product_id report_year total_amount
LC Phone 1 2019 3500
LC T-Shirt 2 2018 310
LC T-Shirt 2 2019 3650
LC T-Shirt 2 2020 10
LC Keychain 3 2019 31
LC Keychain 3 2020 31
💡 Note:

LC Phone sold from 2019-01-25 to 2019-02-28 (35 days in 2019): 35 × 100 = 3500. LC T-Shirt period spans multiple years: 31 days in 2018, 365 days in 2019, 1 day in 2020. LC Keychain spans 2019 (31 days) and 2020 (31 days).
Example 2 — Single Year Period
Input Tables:
Product
product_id product_name
1 LC Phone
Sales
product_id period_start period_end average_daily_sales
1 2018-01-01 2018-12-31 5
Output:
product_name product_id report_year total_amount
LC Phone 1 2018 1825
💡 Note:

Simple case where the entire sales period falls within a single year 2018. Total days: 365, so total amount = 365 × 5 = 1825.

Constraints

  • 1 ≤ Product.product_id ≤ 1000
  • 1 ≤ Sales.product_id ≤ 1000
  • Sales.period_start and period_end are between 2018-01-01 and 2020-12-31
  • period_start ≤ period_end
  • 1 ≤ average_daily_sales ≤ 100

Visualization

Tap to expand
Total Sales Amount by YearSales Period2019-01-25 to 2019-02-2835 days × 100 = 3500Split by YearMulti-Year Period Example201831 days2019365 days20201 dayPeriod: 2018-12-01 to 2020-01-01Daily Sales: 10Final Resultproduct_idyearamount220183102201936502202010
Understanding the Visualization
1
Input
Product and Sales tables with multi-year periods
2
Split Periods
Calculate overlapping days per year
3
Calculate
Multiply daily average by days in each year
Key Takeaway
🎯 Key Insight: Use cross join with date arithmetic to handle sales periods spanning multiple years

Related Problems

Asked in
Amazon 28 Apple 15 Facebook 22
28.5K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen