How To Calculate Month over Month Growth in PostgreSQL

Last updated on May 17th, 2020 at 06:14 am

Month over Month growth is a useful business metric KPI for month over month comparison of sales, signups, etc. We will calculate monthly percentage growth via SQL query, since there is no PostgreSQL function for it. Here’s how to calculate month over month growth in PostgreSQL.

How To Calculate Month over Month Growth in PostgreSQL

Let’s say you have a table that contains monthly sales called sales_data(month, sale)

postgres=# create table sales_data(month int,sale int);

postgres=# insert into sales_data(month,sale) values(1,2021),
           (2,2102),(3,2150),(4,2312),(5,2425);

postgres=# select * from sales_data;
 month | sale
-------+------
     1 | 2021
     2 | 2102
     3 | 2150
     4 | 2312
     5 | 2425

Here’s the SQL query to calculate month over month percentage growth in sales in PostgreSQL.

postgres=# select month,sum(sale) as current_sale,
                lag(sum(sale), 1) over (order by month) as previous_month_sale,
                (100 * (sum(sale) - lag(sum(sale), 1) over (order by month)) / lag(sum(sale), 1) over 
                (order by month)) || '%' as growth
                from sales_data
                group by 1
                order by 1;
 month | current_sale | previous_month_sale | growth
-------+--------------+---------------------+--------
     1 |         2021 |                     |
     2 |         2102 |                2021 | 4%
     3 |         2150 |                2102 | 2%
     4 |         2312 |                2150 | 7%
     5 |         2425 |                2312 | 4%

In the above query, we use the LAG window function to calculate previous month sales, and use it to calculate month over month growth in PostgreSQL. LAG allows you to fetch the row that comes before the present row, at a defined offset, which is 1 in our case.

Bonus read: How to Create Pivot Table in PostgreSQL

In most cases, you will have daily sales data, instead of monthly numbers. In this case, we first aggregate it to monthly sales numbers and then apply the above SQL query to calculate month over month growth in PostgreSQL.

Let’s say you have daily sales data.

postgres=# create sales (order_date date, sale int);

postgres=# insert into sales values('2020-01-01',20),
           ('2020-01-02',25),('2020-01-03',15),('2020-01-04',30),
           ('2020-02-05',20),('2020-02-10',20),('2020-02-06',25),
           ('2020-03-07',15),('2020-03-08',30),('2020-03-09',20);

postgres=# select * from sales;
 order_date | sale | 
------------+------+
 2020-01-01 |   20 |
 2020-01-02 |   25 |
 2020-01-03 |   15 |
 2020-01-04 |   30 |
 2020-02-05 |   20 |
 2020-02-10 |   20 |
 2020-02-06 |   25 |
 2020-03-07 |   15 |
 2020-03-08 |   30 |
 2020-03-09 |   20 |

Here’s the SQL query to calculate monthly growth rate in PostgreSQL

postgres=# select month,sum(sale) as current_sale,
           lag(sum(sale), 1) over (order by month) as previous_month_sale,
           round((100 * (sum(sale) - lag(sum(sale), 1) over (order by month)) / lag(sum(sale), 1) over 
           (order by month)),2) || '%' as growth
           from (select to_char(order_date,'Mon') as month,
           sum(sale) as sale
           from sales
           group by 1 ) sales_data
           group by 1
           order by 1;
 month | current_sale | previous_month_sale | growth
-------+--------------+---------------------+---------
 Feb   |           65 |                     |
 Jan   |           90 |                  65 | 38.46%
 Mar   |           65 |                  90 | -27.78%

In the above query, we aggregate the daily sales data to monthly values (in bold) and then use earlier query.

Also read, How to Calculate Retention Rate in SQL

You can also filter your data, using a WHERE clause, before calculating month over month growth in PostgreSQL.

postgres=# select month,sum(sale) as current_sale,
           lag(sum(sale), 1) over (order by month) as previous_month_sale,
           round((100 * (sum(sale) - lag(sum(sale), 1) over (order by month)) / lag(sum(sale), 1) over 
           (order by month)),2) || '%' as growth
           from (select to_char(order_date,'Mon') as month,
           sum(sale) as sale
           from sales 
           WHERE condition
           group by 1 ) sales_data
           group by 1
           order by 1;

After you calculate month over month growth in PostgreSQL, you can use a reporting tool to plot this data on a bar chart or dashboard and share it with your team. Here’s an example of a bar chart that shows month over month percent growth, created using Ubiq.