Tracking Month Over Month Growth in SQL
In analyzing a business I commonly look at reports that have two lenses, one is by doing various cohort analysis. The other is that I look for Month over Month or Week over Week or some other X over X growth in terms of a percentage. This second form of looking at data is relevant when you’re in a SaaS business or essentially anythign that does recurring billing. In such a business focusing on your MRR and working on growing your MRR is how success can often be measured.
I’ll jump write in, first lets assume you have some method of querying your revenue. In this case you may have some basic query similar to:
SELECT date_trunc('month', mydate) as date, sum(mymoney) as revenue FROM foo GROUP BY date ORDER BY date ASC;
This should give you a nice clean result:
date | revenue ------------------------+---------- 2013-10-01 00:00:00+00 | 10000 2013-11-01 00:00:00+00 | 11000 2013-12-01 00:00:00+00 | 11500
Now this is great, but the first thing I want to do is start to see what my percentage growth month over month is. Surprise, surprise, I can do this directly in SQL. To do so I’ll use a window function and then use the lag function. According to the Postgres docs
lag(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
Essentially it orders it based on the window function and then pulls in the value from the row before. So in action it looks something like:
SELECT date_trunc('month', mydate) as date, sum(mymoney) as revenue, lag(mymoney, 1) over w previous_month_revenue FROM foo WINDOW w as (order by date) GROUP BY date ORDER BY date ASC;
Combining to actually make it a bit more pretty (with some casting to a numeric and then formatting a bit) in terms of a percentage:
SELECT date_trunc('month', mydate) as date, sum(mymoney) as revenue, round((1.0 - (cast(mymoney as numeric) / lag(mymoney, 1) over w)) * 100, 1) myVal_growth FROM foo WINDOW w as (order by date) GROUP BY date ORDER BY date ASC;
And you finally get a nice clean output of your month over month growth directly in SQL:
date | revenue | growth ------------------------+----------+-------- 2013-10-01 00:00:00+00 | 10000 | null 2013-11-01 00:00:00+00 | 11000 | 10.0 2013-12-01 00:00:00+00 | 11500 | 4.5
Find this article helpful? I welcome your support on Patreon to be able to devote more time to articles like this that others find useful.