Business SQL: How to Calculate Revenue Growth in SQL

Can you use SQL in business? Of course you can! For example, you can calculate revenue growth in SQL – here’s how.

As someone who works with data, you already know that SQL is the most used tool in data analysis. But how is SQL helpful in business use? Well, the answer is obvious: analyze business data using SQL! However, there are some specifics with business data that can be tricky if you’re not used to it. These specifics also call for some SQL functions you may not have used yet: window functions.

If you’re not familiar with SQL’s window functions, I recommend you learn a bit about them before continuing. This window function overview will give you a good start, as will this article on window functions with examples.

If you’re already somewhat familiar with window functions and you need a quick refresher before digging in, our SQL Window Functions Cheat Sheet will help you understand the examples I’ll show you.

Why Calculate Revenue Growth?

Revenue growth is an important metric in any business. Whether it’s realized (actual) or projected, revenue growth stats are the basis for business planning and decision making. Salaries, new investments, share price, you name it; everything is based on the revenue growth (or decline).

However, calculating revenue growth in SQL is not that easy. Why not? Because it requires getting the difference between two different rows, not two different columns. Any SQL rookie can find the difference between two columns. But how do you subtract rows?

Using the LEAD() and LAG() Functions to Calculate Revenue Growth

Both LEAD() and LAG() are window functions. The LEAD() function allows you to get data from a subsequent row and use it in the current row. The LAG() function is exactly the opposite; you use it to get the data from a previous row.

Before we go any further, you should make yourself familiar with the syntax of both functions.

Example

In this example, you’ll be using the table monthly_revenue. This table has the following columns:

• id – The month’s ID and the table’s primary key.
• month – The month.
• revenue – The revenue amount.

The table contains monthly revenue for two years, 2019 and 2020. You need to calculate the monthly revenue growth. Additionally, for every month, you need to show the revenue value for the same month of the following year.

Before I write my code, I want to make sure you understand the logic. Once you understand it, writing your own code will be much easier.

Monthly revenue growth involves simple mathematics – subtraction. You need to take the current month’s revenue and deduct the previous month’s revenue. For example:

You can’t calculate the revenue growth for 2019-01-31 because there’s no previous period to compare it with. But you can do it for 2019-02-28. How? It’s simple: 1,348,523.26 - 1,237,844.22 = 110,679.04.

How about getting the revenue from that month in the next year (e.g. comparing January 2019 and January 2020)? Here’s what you need to do:

Suppose your data looks like this; you somehow need to jump 12 rows to get the data. For 2019-01-31, you need to show the current value, which is 1,237,844.22. But you also need to get the data for 2020-01-31 (1,645,112.22) and put it in the column right beside the current revenue.

Now, it’s time to write the problem-solving code:

SELECT	month,
		revenue,
		revenue - LAG (revenue) OVER (ORDER BY month ASC) AS revenue_growth,
		LEAD (revenue, 12) OVER (ORDER BY month ASC) AS next_year_revenue
FROM monthly_revenue;

This code starts by selecting the columns month and revenue; you need this data in your result. The next line uses the LAG() function to calculate the revenue growth over the previous month. It just translates the logic I explained to you into a code. Remember, LAG() gets the data from the previous row. (Note: LAG() can return data from n rows back if you use the optional offset argument, i.e. LAG(column_name, n). If you omit the offset argument, it returns the value from the previous row.) So this line takes the current row’s revenue column and deducts the previous row’s revenue value from it; the previous row is the previous month’s revenue.

Note that LAG() is a window function, so it’s mandatory to include the OVER() clause. In our OVER(), there’s an ORDER BY clause which says the window function should be performed according to the month, in ascending order. In other words, it will start from 2019-01-31 and go all the way to 2020-12-31, month by month. I’ve named this column revenue_growth.

The following code line uses the LEAD() window function. Remember, this function allows you to fetch the data from an upcoming row. You can determine how many rows you want the function to “jump”, just as you can with LAG(). In this case, I need the data 12 rows from the current row; that’s why the 12 offset is inside the function parentheses. Once again, we have an OVER() clause with the same principle as above: the function will be performed according to month ascending. I’ve named this column next_year_revenue.

It’s not that hard once you understand the logic, right? Here’s the result:

The NULL value in the revenue_growth column means there’s no data before 2019-01-31.

The NULL in next_year_revenue means there is no data 12 months from that month.

You can find more chances to practice the LAG() function in this article about calculating the difference between two rows.

How about adding a little something to the result above? Maybe the percentage of revenue growth?

Calculating Revenue Growth Percentages

Showing growth as a percentage is usually even more helpful than showing the absolute values. Management boards especially like to think in percentages. It would be very helpful if your report contained this data too.

To get this result, we can use the same data and the same code as above. We just add one code line to it:

SELECT	month,
		revenue,
		revenue - LAG (revenue) OVER (ORDER BY month ASC) AS revenue_growth,
		(revenue - LAG (revenue) OVER (ORDER BY month ASC))/LAG (revenue) OVER (ORDER BY month ASC)*100 AS revenue_percentage_growth,
		LEAD (revenue, 12) OVER (ORDER BY month ASC) AS next_year_revenue
FROM monthly_revenue;

Here’s the logic for calculating percentage growth:

(current month revenue - previous month revenue)/previous month revenue * 100. 

For example:

The revenue percentage growth for 2019-02-28 is calculated like this:

(1,348,523.26 - 1,237,844.22)/1,237,844.22 * 100 = 8.94%.

In the code above, this is the line that does exactly that:

(revenue - LAG (revenue) OVER (ORDER BY month ASC))/LAG (revenue) OVER (ORDER BY month ASC)*100 AS revenue_percentage_growth. 

There’s probably no need to explain it again. You understand how the LAG() function works and the mathematics behind the calculation. Here’s the result:

Now that you’ve learned what you came for, maybe it’s time for something more? For example, some advanced SQL queries you could use in financial analysis?

Want to Work on Your Professional Growth, Too?

I think it’s helpful to know how to calculate revenue growth. As a data analyst, I’ve often been asked to create reports similar to what I’ve shown you here. This is important data that is constantly analyzed in business. Now that you’ve learned to use LAG() and LEAD() functions, you’re able to use them on any kind of data. In business, you’ll have plenty of opportunities for that.

In case you don’t have a way to practice SQL window functions – or if you just want to brush up your skills – taking a Window Functions course might be a good decision. And if you’re wondering why, here’s an article with the answer to your question.