SQL Query for Month-over-Month Growth Rate (MySQL & PostgreSQL)

The Problem

You have a revenue or users table with daily/monthly data, and you need to calculate the percentage change from one month to the next. This is one of the most common analytics queries — and one that trips people up with date truncation and NULL handling.

MySQL Solution (using LAG window function)

WITH monthly AS (SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, SUM(amount) AS revenue FROM orders GROUP BY 1) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct FROM monthly;

PostgreSQL Solution (using DATE_TRUNC)

WITH monthly AS (SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) AS growth_pct FROM monthly;

Key Differences

MySQL uses DATE_FORMAT for month truncation; PostgreSQL uses DATE_TRUNC. PostgreSQL needs NULLIF to avoid division by zero. MySQL 8.0+ supports window functions; older versions need a self-join approach.

Try It Yourself

Use our free SQL generator — just type "calculate month over month growth rate from an orders table" and select your dialect.