Calculating Year-over-Year (YoY) Sales Growth Using SQL

Introduction

In financial analysis, it is crucial to understand the growth rate of sales over time. Two commonly used metrics for analyzing sales growth are Year-over-Year (YoY) and Month-over-Month (MoM) comparisons. YoY compares sales data from the same month or quarter of different years, while MoM compares sales data from consecutive months. In this article, we will explore how to calculate YoY sales growth using SQL.

All Sales Dashabord Display Some Sort of YoY Metrics on Top

Note that all code below only works for Postgresql. You can easily find MySQL versions by pasting the code into ChatGPT.

Building Mock Data

Before we dive into the calculations, let’s assume we have a sales table with the following structure

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10, 2)
);

Insert some data into the tables using

INSERT INTO sales (id, sales_date, amount)
VALUES
    (1, '2022-01-01', 1000),
    (2, '2022-02-01', 1200),
    (3, '2022-03-01', 1500),
    (4, '2023-01-01', 1800),
    (5, '2023-02-01', 2000),
    (6, '2023-03-01', 2200);

and with a simple select * from sales query, you should get the following

YoY Calculation

We first build a temporary table using the with clause to calculate the total sales for each year,

WITH t AS (
    SELECT
        EXTRACT (YEAR FROM sales_date) AS year,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY EXTRACT (YEAR FROM sales_date)
)
SELECT * FROM t

The temporary table looks as follows:

To calculate YoY, self-join the table to get the previous year’s sales and apply the formula YoY = (cur-past)/past. Voila! YoY is calculated.

-- Sol #1
WITH t AS (
  ...
)
SELECT
    t_cur.year AS year,
    t_cur.total_sales AS total_sales_cur_year,
    t_pre.total_sales AS total_sales_prev_year,
    ROUND((t_cur.total_sales - t_pre.total_sales) / t_pre.total_sales, 2) AS growth_rate
FROM
  t t_cur left join t t_pre on t_cur.year = t_pre.year + 1

Resulting YoY (growth rate):

Alternatively, if the table has no missing year, use lag and over to get the previous year’s sale instead of a self-join:

-- Sol #2
WITH t AS (
  ...
)
SELECT
    *,
    LAG(total_sales, 1) OVER (ORDER BY year) AS total_sales_prev_year,
    ROUND((total_sales - LAG(total_sales, 1) OVER (ORDER BY year)) / LAG(total_sales, 1) OVER (ORDER BY year), 2) AS growth_rate
FROM
  t

Note that the last query called LAG(total_sales, 1) OVER (ORDER BY year) three times. To reduce redundancy, consider using another temporary table:

-- Sol #3
WITH t AS (
  ...
),
t2 AS (
    SELECT
        *,
        LAG(total_sales) OVER (ORDER BY year) AS prev_year_sales
    FROM t
)
SELECT
    *,
    ROUND((total_sales - prev_year_sales) / prev_year_sales * 100, 2) AS growth_rate
FROM t2

Query end result:

MoM can be calculated in a similar way.

Conclusion

Utilizing SQL to calculate year-over-year (YoY) and month-over-month (MoM) growth provides a convenient and efficient approach for monitoring business performance and recognizing patterns over time.

By employing the methods mentioned earlier, you can gain a deeper understanding of your company’s expansion and make well-informed decisions regarding future steps.