SQL Recipes Growth Rates Calculating Exponential Growth Rate

Modeling Exponential Growth in SQL

Quite often the output of a business – for example: revenue, number of employees – is not a straight line. A straight line has a constant slope. Instead, the revenue of a growing business is most likely a constant percentage. This is why magazines like Inc 5000 rank businesses by their growth rates rather than absolute revenue numbers.

If you want to value a business at a future time, you'll want to fit a trend line to past revenues to forecast future revenues. This is where the exponential growth model comes into play.

1. The equation behind exponential curves

The exponential function has the form:

where,

is the observation (independent variable which could be time), is the constant for the base of natural logarithms and is the dependent variable – which could be revenue.

The values for and determine the parameters of the exponential function.

2. Sample data to follow along

We are going to be working with a small data set that you can follow along step-by-step (or on paper or Excel if you wish):

Given a table with data for when a new user starts paying for a subscription:

select date_trunc('day', dt), count(*) from transactions group by 1;

we see something like this:

Assuming no user cancels their subscription, a cumulative/running sum of our subscribers gets us this:

select date_trunc('month', dt) as dt, sum(count(*)) over (order by dt) as total_users_monthly from transactions group by 1;

dt total_users_monthly Jan 2017 597 Feb 2017 595 Mar 2017 997 Apr 2017 1407 May 2017 2215 Jun 2017 3577

This looks exponential!

3. Linearizing the exponential curve

The slope of the exponential curve is not constant, therefore trying to fit a linear line to the curve would be awkward. Sometimes, the percentage slope of the curve is constant, which means that the actual slope of the curve is rapidly increasing. This is where an exponential fit would make sense.

One way to linearize the curve is to take the log of the data.

select date_trunc('month', dt) as dt, sum(count(*)) over (order by dt) as total_users_monthly log(sum(count(*)) over (order by dt)) as log_total_users_monthly from transactions group by 1;

dt total_users_monthly log_total_users_monthly Jan 2017 597 2.775974331 Feb 2017 595 2.774516966 Mar 2017 997 2.998695158 Apr 2017 1407 3.148294097 May 2017 2215 3.345373731 Jun 2017 3577 3.55351894

4. Finding the best fit line using Simple Linear Regression

We will now fit a simple linear regression over this line to figure out the coefficients of the best fit line. We've described the method of linear regression with SQL in great detail elsewhere on our site. On that page, we built the linear regression method from scratch, but here were are going to cheat and make use of PostgreSQL's regr_slope and regr_intercept inbuilt functions to calculate the coefficients of the best fit line.

One additional technicality here is that the regr_slope and regr_intercept functions expect numerical arguments and won't work with our datetime data types on the x-axis. Since the data points are equidistant, we can map these dates to contiguous integers which we'll do using the row_number window function. The row_number window analytical function enumerates each row in the result set with the partition, starting from 1.

select date_trunc('month', dt) as dt, row_number() over (order by date_trunc('month', dt)) as dp, sum(count(*)) over (order by dt) as total_users_monthly log(sum(count(*)) over (order by dt)) as log_total_users_monthly from transactions group by 1

dt dp total_users_monthly log_total_users_monthly Jan 2017 1 597 2.775974331 Feb 2017 2 595 2.774516966 Mar 2017 3 997 2.998695158 Apr 2017 4 1407 3.148294097 May 2017 5 2215 3.345373731 Jun 2017 6 3577 3.55351894

select regr_slope(log_total_users_monthly, dp), regr_intercept(log_total_users_monthly, dp) from ( select date_trunc('month', dt) as dt, row_number() over (order by date_trunc('month', dt)) as dp, log(sum(count(*)) over (order by dt)) as log_total_users_monthly from transactions group by 1 ) b

regr_slope regr_intercept 0.164282636534649 2.52440630934746

Our best-fit line has the equation:

5. Forecasting using the best-fit line

Next, we can extrapolate the line to the next few months to predict what our total new users will be in the future.

First, we generate a few more months with the generate_series function (here we generate six more months):

select 6 + generate_series(1, 6, 1);

Then, we use the equation of the best fit line to extrapolate:

with ext as ( select generate_series(1, 6, 1) + 6 as x, 0.164282636534649 as slope, 2.5244063093474 as intercept ) select ext.slope * ext.x + ext.intercept as y, ext.x from ext

Then, we reverse-map the x-axis back to date time formats:

with ext as ( select generate_series(1, 6, 1) + 6 as x, 0.164282636534649 as slope, 2.5244063093474 as intercept ) select '2017-01-01'::date + (ext.x - 1) * '1 month'::interval as dt, ext.slope * ext.x + ext.intercept as log_total_users_monthly, ext.x as x from ext

which gives us:

dt log_total_users_monthly x Jul 2017 3.674384765089943 7 Aug 2017 3.838667401624592 8 Sep 2017 4.002950038159241 9 Oct 2017 4.16723267469389 10 Nov 2017 4.331515311228539 11 Dec 2017 4.495797947763188 12

Then, we invert the linearized equation using the pow function to forecast total users for the next few months:

with ext as ( select generate_series(1, 6, 1) + 6 as x, 0.164282636534649 as slope, 2.5244063093474 as intercept ) select '2017-01-01'::date + (ext.x -1 ) * '1 month'::interval as dt, pow(10, ext.slope * ext.x + ext.intercept) as total_users_monthly from ext

which gives us:

dt total_users_monthly Jul 2017 4724.8145289282065 Aug 2017 6897.113956704491 Sep 2017 10068.158366961097 Oct 2017 14697.134705694105 Nov 2017 21454.34752656913 Dec 2017 31318.283257788567

6. The complete query

We can union the real and forecasted values to build a complete chart:

select date_trunc('month', dt) as dt, sum(count(*)) over (order by dt) as total_users_monthly from transactions group by 1; union with ext as ( select generate_series(1, 6, 1) + 6 as x, 0.164282636534649 as slope, 2.5244063093474 as intercept ) select '2017-01-01'::date + (ext.x - 1) * '1 month'::interval as dt, pow(10, ext.slope * ext.x + ext.intercept) as total_users_monthly from ext

To separate out the two curves, we can introduced another pivot column to label the series:

select date_trunc('month', dt) as dt, 'actual revenue' as revenue, sum(count(*)) over (order by dt) as total_users_monthly from transactions group by 1; union with ext as ( select generate_series(1, 6, 1) + 6 as x, 0.164282636534649 as slope, 2.5244063093474 as intercept ) select '2017-01-01'::date + (ext.x - 1) * '1 month'::interval as dt, 'projected revenue' as revenue, pow(10, ext.slope * ext.x + ext.intercept) as total_users_monthly from ext