Example 4: Year-Over-Year Sales Change

This SQL query calculates the year-over-year (YoY) change in sales amounts by comparing the sales amount of each year with the previous year’s sales amount. It returns the year, the sales amount for that year, and the YoY change in sales.

SELECT year, 
       sales_amount, 
       sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY year) AS yoy_change
FROM yearly_sales
ORDER BY year;

Query Breakdown:

1. SELECT Clause:
   • year: Retrieves the year of the sales data.
   • sales_amount: Retrieves the sales amount for that specific year.
   • sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY year) AS yoy_change: This is the calculation of the YoY change in sales amounts.
2. Window Function Explanation:
   • LAG(sales_amount, 1, 0): This window function fetches the sales amount from the previous row (i.e., the previous year).
     • sales_amount: The column to look back on.
     • 1: The offset, indicating how many rows back to look.
     • 0: The default value if there is no previous row.
   • OVER (ORDER BY year): Defines the window of rows for the function, ordering them by year.
3. YoY Change Calculation:
   • sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY year): Subtracts the previous year’s sales amount from the current year’s sales amount to calculate the YoY change.
4. FROM Clause:
   • FROM yearly_sales: Specifies the table from which to retrieve the data.
5. ORDER BY Clause:
   • ORDER BY year: Orders the final result set by year to ensure the changes are presented in chronological order.

Detailed Step-by-Step Execution

Initial State: Assume the yearly_sales table has the following data:

Processing Each Year:

• First Year (2020):
  • YoY Change = 100000 - 0 (default value as there is no previous year) = 100000
• Second Year (2021):
  • YoY Change = 150000 - 100000 = 50000
• Third Year (2022):
  • YoY Change = 130000 - 150000 = -20000
• Fourth Year (2023):
  • YoY Change = 170000 - 130000 = 40000

Final Output: