Objective

Given a table of user spending transactions, calculate the year-on-year growth rate of total spend for each product. Include both the current and previous year's spend, along with the percentage change.

Table Schema:

• user_spend

Task Requirements:

• Calculate the total spend for each product per year
• For each product-year combination, include the previous year's total spend
• Compute the year-on-year growth percentage as: (curr_year_spend - prev_year_spend) / prev_year_spend * 100, rounded to 2 decimal places
• For the first year of each product, the previous year's spend and growth percentage should be NULL
• Order results by product_id and year in ascending order
  
  

Output columns: product_id, year, curr_year_spend, prev_year_spend, yoy_growth_pct

WITH yearly_spend AS (
    SELECT
        product_id,
        EXTRACT(YEAR FROM transaction_date)::INT AS year,
        SUM(spend) AS curr_year_spend
    FROM user_spend
    GROUP BY product_id, EXTRACT(YEAR FROM transaction_date)
)
SELECT
    product_id,
    year,
    curr_year_spend,
    LAG(curr_year_spend) OVER (
        PARTITION BY product_id ORDER BY year
    ) AS prev_year_spend,
    ROUND(
        (curr_year_spend - LAG(curr_year_spend) OVER (
            PARTITION BY product_id ORDER BY year
        )) * 100.0
        / LAG(curr_year_spend) OVER (
            PARTITION BY product_id ORDER BY year
        )
    , 2) AS yoy_growth_pct
FROM yearly_spend
ORDER BY product_id, year;

Explanation

Step 1: Aggregate yearly spend per product

WITH yearly_spend AS (
    SELECT
        product_id,
        EXTRACT(YEAR FROM transaction_date)::INT AS year,
        SUM(spend) AS curr_year_spend
    FROM user_spend
    GROUP BY product_id, EXTRACT(YEAR FROM transaction_date)
)

The CTE yearly_spend groups all transactions by product_id and year. It uses EXTRACT(YEAR FROM transaction_date) to pull the year from each record, then applies SUM(spend) to get the total spend per product per year. The ::INT cast converts the extracted year from a floating-point value to a clean integer.

Step 2: Retrieve the previous year's spend with LAG()

LAG(curr_year_spend) OVER (
    PARTITION BY product_id ORDER BY year
) AS prev_year_spend

The LAG() window function looks back one row within the same product_id partition, ordered by year. This gives each row access to the previous year's total spend for that product. For the earliest year of each product, LAG() returns NULL since there is no prior row.

Step 3: Calculate the growth percentage

ROUND(
    (curr_year_spend - LAG(curr_year_spend) OVER (
        PARTITION BY product_id ORDER BY year
    )) * 100.0
    / LAG(curr_year_spend) OVER (
        PARTITION BY product_id ORDER BY year
    )
, 2) AS yoy_growth_pct

The year-on-year growth rate is computed as (curr_year_spend - prev_year_spend) / prev_year_spend * 100.0. Multiplying by 100.0 (instead of 100) ensures decimal division. The ROUND(..., 2) function limits the result to 2 decimal places. For rows where prev_year_spend is NULL, the entire expression evaluates to NULL.

Step 4: Order the results

ORDER BY product_id, year

The final ORDER BY product_id, year ensures the output is grouped by product and sorted chronologically within each product.