Total Sales Amount by Year - Solution & Explanation

HardPremiumFree on FleetCodeDatabase4 min read

Problem Statement

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.

Approach Overview

Problem Overview: You are given product information and sales periods with an average_daily_sales. Each sales record spans a date range, but the report must show total sales per product per year. The challenge is splitting a single date range across multiple years and calculating the exact number of overlapping days for each year.

Approach 1: Year Expansion + Date Overlap Calculation (O(n * y) time, O(y) space)

The practical SQL solution expands each sales record across the years it overlaps, then calculates how many days of that record fall inside each year. You create a small derived table containing all report years (for example 2018–2020). For every sale record, join it with those years where the sale period intersects the year. The overlap days are computed using LEAST() and GREATEST() to clamp the range inside the year boundaries. Multiply the overlapping day count by average_daily_sales and aggregate using SUM(). This pattern is common in SQL problems involving time intervals and partial overlaps.

The key insight is converting a continuous date range into discrete yearly segments. Instead of iterating day by day, you compute the intersection between two ranges: [period_start, period_end] and [year_start, year_end]. That intersection length gives the exact contribution of the sales record to that year. A final GROUP BY product_id, report_year produces the required totals.

Approach 2: Recursive CTE Year Generation (O(n * y) time, O(y) space)

If the year range is not fixed, a recursive CTE can generate all years between the minimum period_start and maximum period_end. Each recursive step increments the year until the upper bound is reached. After generating the year table, the same date-overlap logic applies. This avoids hardcoding report years and adapts automatically to new data ranges.

This method is slightly heavier but more flexible. Many database interview problems expect candidates to handle dynamic ranges rather than relying on static values. The rest of the query still uses joins and aggregation to compute yearly totals.

Recommended for interviews: The year-expansion join with overlap calculation is the most common and readable solution. It demonstrates strong understanding of SQL date arithmetic, range intersection, and aggregation. The recursive CTE variant shows deeper SQL knowledge and is useful when the report years cannot be predefined.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Year Expansion + Date Overlap	O(n * y)	O(y)	Best for fixed reporting years and most interview scenarios
Recursive CTE Year Generation	O(n * y)	O(y)	Useful when the year range must be derived dynamically from the dataset

Video Solution

LeetCode 1384 - Total Sales Amount By Year | LeetCode SQL Solution [HARD] • Code with Carter • 1,512 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Total Sales Amount by Year easy or hard?

LeetCode classifies this problem as Hard because it requires careful reasoning about date ranges and partial overlaps. Candidates must correctly compute intersections between time intervals and perform grouped aggregation, which is more complex than typical SQL filtering tasks.

Total Sales Amount by Year Python/Java solution

The canonical solution is written in SQL (commonly MySQL). Python or Java would typically load the data and simulate the same logic: iterate through sales records, split ranges by year boundaries, compute overlapping days, and aggregate totals using a map keyed by (product_id, year).

How to solve Total Sales Amount by Year in O(n)?

Pure O(n) processing is not practical because each sales record may span multiple years. The standard approach joins sales with a small set of report years and computes date overlaps, resulting in O(n * y) complexity where y is usually small (often 3–10 years).

What is the best approach for Total Sales Amount by Year?

The most practical solution expands each sales record across the years it overlaps and calculates the intersection between the sales period and each year. SQL functions like LEAST() and GREATEST() determine the overlapping days. The yearly total is then computed as overlapping_days * average_daily_sales and aggregated using GROUP BY.

Is Total Sales Amount by Year asked at Google/Amazon/Meta?

SQL interval and reporting problems similar to this frequently appear in data engineering and analytics interviews at companies like Amazon, Meta, and Google. They test understanding of date arithmetic, aggregation, and handling overlapping time ranges in relational databases.

What data structure is used in Total Sales Amount by Year?

The problem relies on relational database tables and SQL operations rather than traditional data structures. The main techniques involve joins, derived tables or CTEs for generating years, and aggregation with GROUP BY.

What is the time complexity of Total Sales Amount by Year?

The typical SQL solution runs in O(n * y) time, where n is the number of sales records and y is the number of distinct report years generated. Each sales record is joined with the years it overlaps, followed by aggregation per product and year.

Ready to solve this problem?

Practice Total Sales Amount by Year with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance61.0%

Approaches1

Reading time4 min

Practice this problem

Open in Editor