Skip to main content
Talentd

Year on Year Growth Rate - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon
Practice this problem

Problem Statement

Table: user_transactions

+------------------+----------+
| Column Name      | Type     | 
+------------------+----------+
| transaction_id   | integer  |
| product_id       | integer  |
| spend            | decimal  |
| transaction_date | datetime |
+------------------+----------+
The transaction_id column uniquely identifies each row in this table.
Each row of this table contains the transaction ID, product ID, the spend amount, and the transaction date.

Write a solution to calculate the year-on-year growth rate for the total spend for each product.

The result table should include the following columns:

  • year: The year of the transaction.
  • product_id: The ID of the product.
  • curr_year_spend: The total spend for the current year.
  • prev_year_spend: The total spend for the previous year.
  • yoy_rate: The year-on-year growth rate percentage, rounded to 2 decimal places.

Return the result table ordered by product_id,year in ascending order.

The result format is in the following example.

 

Example:

Input:

user_transactions table:

+----------------+------------+---------+---------------------+
| transaction_id | product_id | spend   | transaction_date    |
+----------------+------------+---------+---------------------+
| 1341           | 123424     | 1500.60 | 2019-12-31 12:00:00 |
| 1423           | 123424     | 1000.20 | 2020-12-31 12:00:00 |
| 1623           | 123424     | 1246.44 | 2021-12-31 12:00:00 |
| 1322           | 123424     | 2145.32 | 2022-12-31 12:00:00 |
+----------------+------------+---------+---------------------+

Output:

+------+------------+----------------+----------------+----------+
| year | product_id | curr_year_spend| prev_year_spend| yoy_rate |
+------+------------+----------------+----------------+----------+
| 2019 | 123424     | 1500.60        | NULL           | NULL     |
| 2020 | 123424     | 1000.20        | 1500.60        | -33.35   |
| 2021 | 123424     | 1246.44        | 1000.20        | 24.62    |
| 2022 | 123424     | 2145.32        | 1246.44        | 72.12    |
+------+------------+----------------+----------------+----------+

Explanation:

  • For product ID 123424:
    • In 2019:
      • Current year's spend is 1500.60
      • No previous year's spend recorded
      • YoY growth rate: NULL
    • In 2020:
      • Current year's spend is 1000.20
      • Previous year's spend is 1500.60
      • YoY growth rate: ((1000.20 - 1500.60) / 1500.60) * 100 = -33.35%
    • In 2021:
      • Current year's spend is 1246.44
      • Previous year's spend is 1000.20
      • YoY growth rate: ((1246.44 - 1000.20) / 1000.20) * 100 = 24.62%
    • In 2022:
      • Current year's spend is 2145.32
      • Previous year's spend is 1246.44
      • YoY growth rate: ((2145.32 - 1246.44) / 1246.44) * 100 = 72.12%

Note: Output table is ordered by product_id and year in ascending order.

Approach Overview

Problem Overview: The task is to compute the year‑on‑year (YoY) growth rate of a metric stored in a database table. For each year (and typically for each entity such as product or company), you aggregate the metric, find the value from the previous year, and calculate the percentage change using (current - previous) / previous.

Approach 1: Grouping Statistics + Left Join (O(n log n) time, O(n) space)

Start by aggregating the metric by year using GROUP BY. This produces one row per year with the total value you want to analyze. Next, self‑join this aggregated dataset with itself using a LEFT JOIN where the current year matches the previous year plus one. The join exposes both the current year's total and the previous year's total in the same row, allowing you to compute the YoY growth rate using a simple arithmetic expression.

This approach relies on standard SQL operations: grouping, joining, and arithmetic expressions. The key insight is that YoY growth is simply a comparison between adjacent years, so joining the dataset with a shifted version of itself solves the alignment problem. A LEFT JOIN ensures that the earliest year still appears even when no previous year exists. Aggregation and sorting during grouping typically lead to O(n log n) processing time in most SQL engines, with O(n) intermediate storage for the grouped results.

This method is widely supported across relational systems and works cleanly in MySQL where window functions may not always be the preferred option. It fits naturally with concepts from SQL, database querying, and relational joins.

Approach 2: Window Function with LAG (O(n log n) time, O(n) space)

An alternative solution uses a window function such as LAG(). After computing yearly aggregates, apply LAG(total_value) ordered by year to access the previous year's value in the same result set. The growth rate can then be calculated directly without a self‑join.

The advantage of this method is readability. Window functions make sequential comparisons explicit and eliminate the need for manual join conditions. Internally, the database still sorts rows by year, so the complexity remains roughly O(n log n) with O(n) memory for the window frame.

Recommended for interviews: The grouping + left join approach is the safest answer. It shows you understand relational joins and how to align records across time periods. Mentioning the window function alternative demonstrates deeper SQL knowledge and awareness of modern query features.

Solution

We can first group by product_id and year(transaction_date) to perform the statistics, then use a left join to associate the statistics of the current year with those of the previous year, and finally calculate the year-on-year growth rate.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Grouping Statistics + Left JoinO(n log n)O(n)Most portable SQL solution; works across MySQL and common relational databases
Window Function (LAG)O(n log n)O(n)Cleaner query when window functions are supported and dataset is already grouped by year

Video Solution

Leetcode HARD 3214 - Year on Year Growth Rate LAG() WINDOW - Explained by Everyday Data Science • Everyday Data Science • 878 views views

Frequently Asked Questions

Is Year on Year Growth Rate easy or hard?
The problem is categorized as Hard because it combines aggregation, time‑based alignment, and percentage calculations in SQL. Candidates must correctly handle joins between consecutive years and ensure edge cases such as missing previous years are handled properly.
Year on Year Growth Rate Python/Java solution
This problem is typically solved with SQL rather than Python or Java because it involves relational aggregation and joins. In application code, you would first aggregate values per year using a map or dictionary, then iterate through sorted years to compute the growth rate.
How to solve Year on Year Growth Rate in O(n)?
In pure SQL, achieving strict O(n) is uncommon because grouping usually requires sorting or hashing. Practically, you aggregate values per year and either join with the previous year or use a window function like LAG(). Both approaches are typically O(n log n) in real database engines.
What is the best approach for Year on Year Growth Rate?
The most reliable solution groups records by year and uses a self or left join to match each year with its previous year. After joining, compute the growth using (current_year_value - previous_year_value) / previous_year_value. This approach works in standard SQL and runs in roughly O(n log n) time due to grouping and sorting.
Is Year on Year Growth Rate asked at Google/Amazon/Meta?
Year‑over‑year growth calculations appear frequently in SQL interviews at data‑focused teams in companies like Amazon, Meta, and analytics roles at Google. The pattern tests aggregation, joins, and understanding of time‑based comparisons in relational databases.
What data structure is used in Year on Year Growth Rate?
The solution relies on relational database tables with grouping and joins rather than traditional in‑memory data structures. Conceptually, the grouped results behave like a mapping from year to aggregated value, which is then aligned with the previous year using a join or window function.
What is the time complexity of Year on Year Growth Rate?
Most SQL implementations process the query in about O(n log n) time because the database groups rows by year and may sort them internally. The join between yearly aggregates is linear relative to the grouped result size. Space usage is O(n) for the intermediate aggregated dataset.

Ready to solve this problem?

Practice Year on Year Growth Rate with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary