Back to articles list June 16, 2021 - 7 minutes read Business SQL: How to Calculate Revenue Growth in SQL Tihomir Babic Tihomir is a financial and data analyst turned database designer from Zagreb, Croatia. He has extensive experience in the financial services industry, which helps him combine his finance background with his interest in data. When not tinkering with data, he likes to play the guitar in a band and pretend to be a musician. He also regularly writes columns for the Croatian LGBT news site CroL.hr and hopes to soon write his first novel. Tags: sql learn sql revenue growth 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: monthrevenue 2019-01-311,237,844.22 2019-02-281,348,523.26 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: monthrevenue 2019-01-311,237,844.22 2019-02-281,348,523.26 2019-03-311,028,423.12 2019-04-301,567,213.49 2019-05-312,108,669.68 2019-06-301,984,632.44 2019-07-31224,557.74 2019-08-312,249,995.11 2019-09-302,104,567.63 2019-10-312,008,412.00 2019-11-302,331,114.50 2019-12-311,978,412.62 2020-01-311,645,112.22 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: monthrevenuerevenue_growthnext_year_revenue 2019-01-311,237,844.22NULL1,645,112.22 2019-02-281,348,523.26110,679.041,025,411.77 2019-03-311,028,423.12-320,100.141,331,224.45 2019-04-301,567,213.49538,790.371,812,225.92 2019-05-312,108,669.68541,456.191,945,331.62 2019-06-301,984,632.44-124,037.242,592,333.88 2019-07-31224,557.74-1,760,074.702,108,496.66 2019-08-312,249,995.112,025,437.372,512,367.31 2019-09-302,104,567.63-145,427.482,662,398.45 2019-10-312,008,412.00-96,155.632,925,568.13 2019-11-302,331,114.50322,702.503,108,469.22 2019-12-311,978,412.62-352,701.883,009,964.39 2020-01-311,645,112.22-333,300.40NULL 2020-02-291,025,411.77-619,700.45NULL 2020-03-311,331,224.45305,812.68NULL 2020-04-301,812,225.92481,001.47NULL 2020-05-311,945,331.62133,105.70NULL 2020-06-302,592,333.88647,002.26NULL 2020-07-312,108,496.66-483,837.22NULL 2020-08-312,512,367.31403,870.65NULL 2020-09-302,662,398.45150,031.14NULL 2020-10-312,925,568.13263,169.68NULL 2020-11-303,108,469.22182,901.09NULL 2020-12-313,009,964.39-98,504.83NULL 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: monthrevenue 2019-01-311,237,844.22 2019-02-281,348,523.26 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: monthrevenuerevenue_growthrevenue_percentage_growthnext_year_revenue 2019-01-311,237,844.22NULLNULL1,645,112.22 2019-02-281,348,523.26110,679.048.941,025,411.77 2019-03-311,028,423.12-320,100.14-23.741,331,224.45 2019-04-301,567,213.49538,790.3752.391,812,225.92 2019-05-312,108,669.68541,456.1934.551,945,331.62 2019-06-301,984,632.44-124,037.24-5.882,592,333.88 2019-07-31224,557.74-1,760,074.70-88.692,108,496.66 2019-08-312,249,995.112,025,437.37901.972,512,367.31 2019-09-302,104,567.63-145,427.48-6.462,662,398.45 2019-10-312,008,412.00-96,155.63-4.572,925,568.13 2019-11-302,331,114.50322,702.5016.073,108,469.22 2019-12-311,978,412.62-352,701.88-15.133,009,964.39 2020-01-311,645,112.22-333,300.40-16.85NULL 2020-02-291,025,411.77-619,700.45-37.67NULL 2020-03-311,331,224.45305,812.6829.82NULL 2020-04-301,812,225.92481,001.4736.13NULL 2020-05-311,945,331.62133,105.707.34NULL 2020-06-302,592,333.88647,002.2633.26NULL 2020-07-312,108,496.66-483,837.22-18.66NULL 2020-08-312,512,367.31403,870.6519.15NULL 2020-09-302,662,398.45150,031.145.97NULL 2020-10-312,925,568.13263,169.689.88NULL 2020-11-303,108,469.22182,901.096.25NULL 2020-12-313,009,964.39-98,504.83-3.17NULL 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. Tags: sql learn sql revenue growth You may also like SQL Course of the Month – Window Functions Find out why you should learn SQL window functions in April and why you should do it in our course. Read more SQL Window Function Example With Explanations Interested in how SQL window functions work? Scroll down to see our SQL window function example with definitive explanations! Read more SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more The LAG Function and the LEAD Function in SQL What are positional functions LAG() and LEAD()? What are the differences, and when do we use them? We’ll look at these window functions in detail. Read more 6 Advanced SQL Queries for Analyzing Financial Data Learn how to analyze financial data with advanced SQL. Six examples will lead you through various SQL practices that will make analyzing financial data much easier! Read more SQL Course of the Month – Analyzing Revenue Trends in SQL During the month of September, the course on Analyzing Revenue Trends in SQL is free. Learn how to create the best SQL reports. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.