Year on Year Growth Rate - Practice Coding Problems

Year on Year Growth Rate - Problem

Database Hard

You are given a table user_transactions containing transaction data with product IDs, spend amounts, and transaction dates.

Task: Calculate the year-on-year growth rate for the total spend for each product.

The result should include:

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

The growth rate formula is: ((curr_year_spend - prev_year_spend) / prev_year_spend) * 100

Return results ordered by product_id and year in ascending order.

Table Schema

user_transactions

Column Name	Type	Description
`transaction_id` PK	integer	Unique transaction identifier
`product_id`	integer	Product identifier
`spend`	decimal	Transaction spend amount
`transaction_date`	datetime	Date and time of transaction

Primary Key: transaction_id

Note: Each row represents a single transaction with product and spend information

Input & Output

Example 1 — Single Product Multiple Years

Input Table:

transaction_id	product_id	spend	transaction_date
1341	123424	1500.6	2019-12-31 12:00:00
1423	123424	1000.2	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.6
2020	123424	1000.2	1500.6	-33.35
2021	123424	1246.44	1000.2	24.62
2022	123424	2145.32	1246.44	72.12

💡 Note:

Product 123424 shows varying year-over-year growth: a 33.35% decline from 2019 to 2020, 24.62% growth from 2020 to 2021, and 72.12% growth from 2021 to 2022. The first year (2019) has NULL values for previous year and growth rate.

Example 2 — Multiple Products

Input Table:

transaction_id	product_id	spend	transaction_date
1001	100	500	2020-06-15 10:00:00
1002	100	300	2021-03-20 14:30:00
1003	200	1000	2020-08-10 16:45:00
1004	200	1200	2021-11-05 09:15:00

Output:

year	product_id	curr_year_spend	prev_year_spend	yoy_rate
2020	100	500
2021	100	300	500	-40
2020	200	1000
2021	200	1200	1000	20

💡 Note:

Two products with different growth patterns: Product 100 declined by 40% from 2020 to 2021, while Product 200 grew by 20%. Results are ordered by product_id first, then by year.

Constraints

1 ≤ transaction_id ≤ 10000
1 ≤ product_id ≤ 1000
spend is a positive decimal value
transaction_date is a valid datetime

Visualization

Tap to expand

Understanding the Visualization

Input

Transaction data with dates and spending

Aggregate

Sum spending by product and year

LAG Function

Access previous year's data

Output

Growth rate calculations

Key Takeaway

🎯 Key Insight: LAG window function efficiently accesses previous row values within partitions, perfect for time-series comparisons

Asked in

A Amazon 28 f Meta 22 N Netflix 18 U Uber 15

Use LAG window function to access previous year's spending data within each product partition. First aggregate spending by year and product, then calculate growth rate using LAG() to get the previous year's value.

Table Schema

user_transactions

Column Name	Type	Description
`transaction_id` PK	integer	Unique transaction identifier
`product_id`	integer	Product identifier
`spend`	decimal	Transaction spend amount
`transaction_date`	datetime	Date and time of transaction

Primary Key: transaction_id

Note: Each row represents a single transaction with product and spend information

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with LAG	O(n log n)	O(n)	Use LAG window function to access previous year's spend data

Window Function with LAG — Algorithm Steps

Step 1: Extract year and sum spending by product and year
Step 2: Use LAG to get previous year's spend for each product
Step 3: Calculate growth rate using current and previous year values

Visualization

Tap to expand

Step-by-Step Walkthrough

Aggregate

Sum spending by year and product

LAG Function

Access previous year's spend

Calculate

Compute growth rate percentage

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for window function ordering

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for aggregated yearly data

⚡ Linearithmic Space

34.5K Views

High Frequency

~18 min Avg. Time

892 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with LAG — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler