Year on Year Growth Rate - Practice Coding Problems

Year on Year Growth Rate - Problem

Database Hard

Year-on-Year Growth Rate Analysis

You are working as a data analyst for an e-commerce company that tracks product performance over multiple years. Your task is to calculate the year-on-year (YoY) growth rate for each product's total spending to help the business understand which products are growing or declining.

Given a table user_transactions containing transaction data, you need to:

1. Aggregate total spending by product and year
2. Calculate the YoY growth rate using the formula: ((current_year - previous_year) / previous_year) × 100
3. Handle edge cases where no previous year data exists (return NULL)

The growth rate helps identify:
• 📈 Growing products (positive YoY rate)
• 📉 Declining products (negative YoY rate)
• 🚀 New products (NULL for first year)

Key Requirements:
• Round growth rates to 2 decimal places
• Order results by product_id and year ascending
• Include NULL values for first year of each product

Input & Output

basic_growth.sql — Basic Growth Calculation

$ Input: user_transactions: 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

› 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

💡 Note: Product 123424 shows declining performance in 2020 (-33.35%) but recovers in 2021 (+24.62%). First year (2019) has NULL growth rate as expected.

multiple_products.sql — Multiple Products

$ Input: user_transactions: transaction_id | product_id | spend | transaction_date 1001 | 111 | 500.00 | 2020-06-15 10:00:00 1002 | 111 | 750.00 | 2021-06-15 10:00:00 1003 | 222 | 1200.00| 2020-03-20 15:00:00 1004 | 222 | 960.00 | 2021-03-20 15:00:00

› Output: year | product_id | curr_year_spend | prev_year_spend | yoy_rate 2020 | 111 | 500.00 | NULL | NULL 2021 | 111 | 750.00 | 500.00 | 50.00 2020 | 222 | 1200.00 | NULL | NULL 2021 | 222 | 960.00 | 1200.00 | -20.00

💡 Note: Two products with opposite trends: Product 111 grows 50%, while Product 222 declines by 20%. Each product's growth is calculated independently.

aggregation_edge_case.sql — Multiple Transactions Per Year

$ Input: user_transactions: transaction_id | product_id | spend | transaction_date 2001 | 333 | 100.00 | 2020-01-15 09:00:00 2002 | 333 | 200.00 | 2020-06-15 14:00:00 2003 | 333 | 150.00 | 2020-12-15 16:00:00 2004 | 333 | 300.00 | 2021-03-10 11:00:00 2005 | 333 | 225.00 | 2021-08-20 13:00:00

› Output: year | product_id | curr_year_spend | prev_year_spend | yoy_rate 2020 | 333 | 450.00 | NULL | NULL 2021 | 333 | 525.00 | 450.00 | 16.67

💡 Note: Multiple transactions per year are correctly aggregated: 2020 total = $450.00 (100+200+150), 2021 total = $525.00 (300+225). Growth rate = 16.67%.

Constraints

1 ≤ number of transactions ≤ 10⁵
1 ≤ product_id ≤ 10⁶
0.01 ≤ spend ≤ 10⁶.00
transaction_date format: YYYY-MM-DD HH:MM:SS
Growth rates must be rounded to 2 decimal places
Results ordered by product_id ASC, year ASC

Visualization

Tap to expand

Understanding the Visualization

Raw Transaction Data

Individual transactions scattered across different dates and products

Annual Aggregation

Group transactions by product and year, sum up total spending

Window Function Magic

LAG() function looks back to previous year within each product partition

Growth Rate Calculation

Apply percentage growth formula: ((current - previous) / previous) × 100

Key Takeaway

🎯 Key Insight: Window functions like LAG() transform complex multi-pass problems into elegant single-pass solutions, making time-series analysis both efficient and intuitive.

Asked in

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

The optimal solution uses SQL window functions with LAG() to access previous year data efficiently. Key steps: (1) Aggregate spending by product and year using GROUP BY, (2) Apply LAG() OVER (PARTITION BY product_id ORDER BY year) to get previous year's spending, (3) Calculate growth rate using the formula ((curr - prev) / prev) × 100, and (4) Handle NULL values for first year appropriately. This approach achieves O(n log n) complexity and scales well with large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries (Brute Force)	O(n²)	O(n)	Use nested subqueries to find previous year data for each product-year combination
Window Functions (Optimal)	O(n log n)	O(k)	Use LAG window function to access previous year data in a single pass

Nested Subqueries (Brute Force) — Algorithm Steps

Create a CTE to aggregate total spending by product and year
For each product-year combination, use a correlated subquery to find previous year's spending
Calculate YoY growth rate using the formula: ((curr - prev) / prev) * 100
Handle NULL cases when no previous year exists
Round results to 2 decimal places and order appropriately

Visualization

Tap to expand

Step-by-Step Walkthrough

Aggregate by Year

First CTE groups transactions by product_id and year

Main Query Scan

For each row in main query, execute subquery

Subquery Execution

Subquery scans entire CTE to find previous year data

Calculate Growth

Apply growth rate formula with previous year data

Code -

solution.c — C

#include <stdio.h>
#include <string.h>

const char* getBruteForceQuery() {
    return "\
    WITH yearly_spend AS (\
        SELECT \
            EXTRACT(YEAR FROM transaction_date) AS year,\
            product_id,\
            SUM(spend) AS curr_year_spend\
        FROM user_transactions\
        GROUP BY EXTRACT(YEAR FROM transaction_date), product_id\
    )\
    SELECT \
        y1.year,\
        y1.product_id,\
        y1.curr_year_spend,\
        (\
            SELECT y2.curr_year_spend \
            FROM yearly_spend y2 \
            WHERE y2.product_id = y1.product_id \
            AND y2.year = y1.year - 1\
        ) AS prev_year_spend,\
        CASE \
            WHEN (\
                SELECT y2.curr_year_spend \
                FROM yearly_spend y2 \
                WHERE y2.product_id = y1.product_id \
                AND y2.year = y1.year - 1\
            ) IS NULL THEN NULL\
            ELSE ROUND(\
                (y1.curr_year_spend - (\
                    SELECT y2.curr_year_spend \
                    FROM yearly_spend y2 \
                    WHERE y2.product_id = y1.product_id \
                    AND y2.year = y1.year - 1\
                )) / (\
                    SELECT y2.curr_year_spend \
                    FROM yearly_spend y2 \
                    WHERE y2.product_id = y1.product_id \
                    AND y2.year = y1.year - 1\
                ) * 100, 2\
            )\
        END AS yoy_rate\
    FROM yearly_spend y1\
    ORDER BY y1.product_id, y1.year;";
}

int main() {
    printf("Brute force approach using nested subqueries\n");
    printf("%s\n", getBruteForceQuery());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n product-year combinations, we perform a subquery that potentially scans all n rows

⚠ Quadratic Growth

Space Complexity

O(n)

Space for intermediate CTE results and final output

⚡ Linearithmic Space

67.2K Views

High 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 (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler