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.

3214. Year on Year Growth Rate - Practice Online

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.