Total Sales Amount by Year - Practice Coding Problems

Total Sales Amount by Year - Problem

Database Hard

You're working as a data analyst for a major e-commerce company that tracks product sales across multiple years. The company needs a comprehensive yearly sales report to understand product performance trends.

The Challenge: Given two tables - Product (containing product information) and Sales (containing sales periods with average daily sales), you need to calculate the total sales amount for each product for each year from 2018 to 2020.

Key Complexity: Sales periods can span across multiple years! For example, a sales period from December 2018 to February 2019 needs to be split and calculated separately for each year.

Your Task: Write a SQL query that breaks down sales periods by year, calculates the total sales for each product in each year, and returns results ordered by product_id and report_year.

Think of it as creating annual financial reports where you need to allocate revenue to the correct fiscal years, even when sales campaigns cross year boundaries.

Input & Output

example_1.sql — Basic Example

$ Input: Product table:\n| product_id | product_name |\n|------------|-------------|\n| 1 | LC Phone |\n\nSales table:\n| product_id | period_start | period_end | average_daily_sales |\n|------------|-------------|------------|-------------------|\n| 1 | 2019-01-25 | 2019-02-28 | 100 |

› Output: | product_id | product_name | report_year | total_amount |\n|------------|-------------|-------------|-------------|\n| 1 | LC Phone | 2019 | 3500 |

💡 Note: Sales period is entirely within 2019. Days from Jan 25 to Feb 28 = 35 days (including both dates). Total = 35 × 100 = 3500.

example_2.sql — Cross-Year Sales

› Output: | product_id | product_name | report_year | total_amount |\n|------------|-------------|-------------|-------------|\n| 1 | LC Phone | 2018 | 3400 |\n| 1 | LC Phone | 2019 | 3000 |

💡 Note: Sales period spans two years. 2018: Dec 15-31 = 17 days × 200 = 3400. 2019: Jan 1-15 = 15 days × 200 = 3000.

example_3.sql — Multiple Products and Periods

$ Input: Product table:\n| product_id | product_name |\n|------------|-------------|\n| 1 | LC Phone |\n| 2 | LC T-Shirt |\n\nSales table:\n| product_id | period_start | period_end | average_daily_sales |\n|------------|-------------|------------|-------------------|\n| 1 | 2019-01-01 | 2019-12-31 | 50 |\n| 2 | 2018-12-01 | 2020-01-31 | 10 |

› Output: | product_id | product_name | report_year | total_amount |\n|------------|-------------|-------------|-------------|\n| 1 | LC Phone | 2019 | 18250 |\n| 2 | LC T-Shirt | 2018 | 310 |\n| 2 | LC T-Shirt | 2019 | 3650 |\n| 2 | LC T-Shirt | 2020 | 310 |

💡 Note: Product 1 sells all of 2019 (365 days × 50 = 18250). Product 2 spans multiple years: 2018 (31 days × 10 = 310), 2019 (365 days × 10 = 3650), 2020 (31 days × 10 = 310).

Constraints

product_id ranges from 1 to 1000
Sales periods are between 2018-01-01 and 2020-12-31
average_daily_sales is a positive integer ≤ 10⁴
Each product can have multiple sales periods
Sales periods can overlap or span multiple years

Visualization

Tap to expand

Understanding the Visualization

Sales Timeline

A sales period from Dec 2018 to Feb 2019

Year Boundaries

Mark the year boundaries (Dec 31, 2018)

Calculate Overlap

Find intersection: Dec 2018 portion and Jan-Feb 2019 portion

Allocate Revenue

Multiply overlap days by daily sales rate for each year

Key Takeaway

🎯 Key Insight: The problem is fundamentally about date range intersections - finding overlap between sales periods and calendar years, then allocating revenue proportionally.

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 28

The optimal solution uses a Common Table Expression (CTE) to generate all years (2018-2020), then cross joins with products to create all product-year combinations. For each combination, it calculates the date range intersection between sales periods and calendar years using GREATEST/LEAST functions, multiplies by daily sales rate, and groups results. This approach is elegant, maintainable, and handles all edge cases including cross-year sales periods.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n)	O(n)	Use multiple nested queries to handle each year separately
Recursive CTE with Years Table (Optimal)	O(n × y)	O(n × y)	Generate years dynamically and join with sales data in a single elegant query

Nested Subqueries Approach — Algorithm Steps

Create separate subqueries for 2018, 2019, and 2020
In each subquery, join Product and Sales tables
Calculate date overlaps with the specific year
Compute total sales as overlap days × average daily sales
Combine all subqueries using UNION ALL
Order final results by product_id and report_year

Visualization

Tap to expand

Step-by-Step Walkthrough

Process 2018

Create subquery for 2018 sales calculation

Process 2019

Create identical subquery for 2019 sales calculation

Process 2020

Create identical subquery for 2020 sales calculation

UNION ALL

Combine all three result sets together

Code -

solution.c — C

// Brute Force SQL Query (C format)
#include <stdio.h>
#include <string.h>

const char* getTotalSalesQuery() {
    return "SELECT product_id, product_name, '2018' as report_year, "
           "       SUM(average_daily_sales * "
           "           (DATEDIFF("
           "               LEAST(period_end, '2018-12-31'), "
           "               GREATEST(period_start, '2018-01-01')"
           "           ) + 1)"
           "       ) as total_amount "
           "FROM Product p JOIN Sales s ON p.product_id = s.product_id "
           "WHERE period_start <= '2018-12-31' AND period_end >= '2018-01-01' "
           "GROUP BY product_id, product_name "
           "UNION ALL "
           "SELECT product_id, product_name, '2019' as report_year, "
           "       SUM(average_daily_sales * "
           "           (DATEDIFF("
           "               LEAST(period_end, '2019-12-31'), "
           "               GREATEST(period_start, '2019-01-01')"
           "           ) + 1)"
           "       ) as total_amount "
           "FROM Product p JOIN Sales s ON p.product_id = s.product_id "
           "WHERE period_start <= '2019-12-31' AND period_end >= '2019-01-01' "
           "GROUP BY product_id, product_name "
           "UNION ALL "
           "SELECT product_id, product_name, '2020' as report_year, "
           "       SUM(average_daily_sales * "
           "           (DATEDIFF("
           "               LEAST(period_end, '2020-12-31'), "
           "               GREATEST(period_start, '2020-01-01')"
           "           ) + 1)"
           "       ) as total_amount "
           "FROM Product p JOIN Sales s ON p.product_id = s.product_id "
           "WHERE period_start <= '2020-12-31' AND period_end >= '2020-01-01' "
           "GROUP BY product_id, product_name "
           "ORDER BY product_id, report_year;";
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Still linear time as we process each sales record once per year, but with higher constant factor due to multiple unions

✓ Linear Growth

Space Complexity

O(n)

Space for storing intermediate results from multiple subqueries

⚡ Linearithmic Space

43.7K Views

Medium Frequency

~25 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler