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.

1384. Total Sales Amount by Year - Practice Online

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.