# 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.

If you want to create charts, dashboards & reports from PostgreSQL database, you can try Ubiq. We offer a 14-day free trial.