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
date count
2016-01-01 10
2016-01-02 12
2016-01-03 15
... ...

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
date count lag
2016-01-01 10
2016-01-02 12 10
2016-01-03 15 12
... ... ...

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
date count growth
2016-01-01 10
2016-01-02 12 20%
2016-01-03 15 25%
... ... ...

👋 No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time. See past emails here.

results matching ""

    No results matching ""