1. SQL Recipes
2. Growth Rates
3. Calculating Month-over-Month Growth Rate

Calculating Month-Over-Month Growth Rate in SQL

To benchmark your business, you’ll want to compute week-over-week, month-over-month and year-over-year growth rates. In the case of Silota, we are not only interested in the number of charts created monthly, but also their growth rates on a month-to-month basis.

select date_trunc('month', timestamp) as date,
       count(*) as count
from events
where event_name = 'created chart'
group by 1
order by 1

The above query should give us a neat table with the number of charts created every month. To compute the growth rates, we use window functions and the lag function. First to understand how the lag function works:

select date_trunc('month', timestamp) as date,
       count(*) as count,
       lag(count(*), 1) over timestamp
from events
where event_name = 'created chart'
group by 1
order by 1

The lag function returns a value evaluated at the row that is definable offset before the current row within the partition. In this particular we have simply picked the value from the previous row (offset of 1). To compute growth rates, it’s just a matter of subtracting the current value from the previous value:

select date_trunc('month', timestamp) as date,
       count(*) as count,
       100 * (count(*) - lag(count(*), 1) over (order by timestamp)) / lag(count(*), 1) over (order by timestamp)) || '%' as growth
from events
where event_name = 'created chart'
group by 1
order by 1