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 salesWHERE conditiongroup 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.