Calculating Year-Over-Year Difference in SQL: An Easy Guide

In business intelligence and data analysis, the analysis of the operation performance metrics against the historical trend is a vital process that contributes to understanding the trends, the opportunity for growth and the taking of right decisions. Such comparison is important because year-over-year difference analysis acts as the basic tool for analysts and business entities to measure the fluctuations of critical metrics from one year to the following one.

Using SQL (Structured Query Language), an efficacious and conducive tool to deal with relational databases enables a clear and systematic way of doing the YoY difference analysis. In this article, we are going to study different techniques to calculate year-over-year differences using SQL. Let’s explore the concept in detail.

What is the Year-Over-Year Difference?

As a financial performance measure, that is a year-over-year difference (YoY difference) it is used to compare the same metric (like sales, revenue, profit, etc.) between two sequential years. The differential is calculated by the formula: which is equal to the difference of value of the metric during the past year and the current year. The formula for calculating year-over-year difference is:

Year-Over-Year Difference=Current year model−Previous year model.

Techniques To Calculate Year-over-Year Difference in SQL

There are two different ways to calculate the year-over-year difference in SQL. The first method involves the use of Self-Join. The second method involves the implementation of Windows functions. Let’s see an example based on both methods.

1. Use of Self Join

In this example, we are using Self-Join to calculate the year-over-year difference. The aggregate functions like SUM() and EXTRACT(), are used to extract the data from the table. The sales table is used here as an input. Let’s see the query for a better understanding.

CREATE TABLE sales (
    date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (date, amount) VALUES
    ('2023-01-01', 1000.00),
    ('2023-02-01', 1500.00),
    ('2023-03-01', 2000.00),
    ('2024-01-01', 1200.00),
    ('2024-02-01', 1800.00),
    ('2024-03-01', 2200.00);

SELECT 
    current.year AS current_year,
    current.amount AS current_amount,
    previous.amount AS previous_amount,
    current.amount - previous.amount AS yoy_difference
FROM
    (SELECT EXTRACT(YEAR FROM date) AS year, SUM(amount) AS amount
     FROM sales
     WHERE EXTRACT(YEAR FROM date) = 2024 -- Placeholder for the current year
     GROUP BY EXTRACT(YEAR FROM date)) AS current
JOIN
    (SELECT EXTRACT(YEAR FROM date) AS year, SUM(amount) AS amount
     FROM sales
     WHERE EXTRACT(YEAR FROM date) = 2023 -- Placeholder for the previous year
     GROUP BY EXTRACT(YEAR FROM date)) AS previous
ON current.year = previous.year + 1;
Code language: SQL (Structured Query Language) (sql)

In the outcome, we can see the difference between the two years is calculated correctly. In this way, you can use your dataset and calculate the year-over-year difference using Self-Join.

2. Use of Window Functions

In this method, we can use the Windows function that is lag() function in a query to calculate the year-over-year difference. The lag() function is used to compare the values of the current row and the previous row from the result set. This query will provide the sales amount for the previous year.

CREATE TABLE sales (
    date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (date, amount) VALUES
    ('2023-01-01', 1000.00),
    ('2023-02-01', 1500.00),
    ('2023-03-01', 2000.00),
    ('2024-01-01', 1200.00),
    ('2024-02-01', 1800.00),
    ('2024-03-01', 2200.00);
    
SELECT 
    current_year,
    current_amount,
    LAG(current_amount) OVER (ORDER BY current_year) AS previous_amount,
    current_amount - LAG(current_amount) OVER (ORDER BY current_year) AS yoy_difference
FROM
    (SELECT EXTRACT(YEAR FROM date) AS current_year, SUM(amount) AS current_amount
     FROM sales
     GROUP BY EXTRACT(YEAR FROM date)) AS yearly_sales;
Code language: SQL (Structured Query Language) (sql)

The results are correct. So, you can replace the table with your dataset and implement the query.

Summary

In this article, we have seen two different techniques to calculate the year-over-year difference using SQL. The year-over-year difference is very important for business analysis and can be utilized effectively for financial evaluation by comparing key financial data points across different years. Hope you will understand the concept and enjoy the article.

Reference

https://stackoverflow.com/questions/33863937/sql-query-year-by-year-growth-of-sales