Comparison vs previous period

Comparing against a previous period means pulling a past value - last month, last quarter, last year - right next to the current one so you can see the gap at a glance.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Comparing Retail Revenue to the Previous Period

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS period,
         SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY 1
)
SELECT period,
       total_revenue,
       LAG(total_revenue) OVER (ORDER BY period) AS prev_revenue,
       total_revenue - LAG(total_revenue) OVER (ORDER BY period) AS revenue_change,
       ROUND((total_revenue - LAG(total_revenue) OVER (ORDER BY period)) / NULLIF(LAG(total_revenue) OVER (ORDER BY period), 0) * 100, 2) AS pct_change
FROM monthly
ORDER BY period

Result:

period	total_revenue	prev_revenue	revenue_change	pct_change
2024-01-01	2050.5
2024-02-01	3141.05	2050.5	1090.55	53.18

Step by step

WITH lets you name a sub-query so the main query can reference it.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS period,
         SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY 1
)

After SELECT, list the column names you want, separated by commas.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS period,
         SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY 1
)
SELECT period,
       total_revenue,
       LAG(total_revenue) OVER (ORDER BY period) AS prev_revenue,
       total_revenue - LAG(total_revenue) OVER (ORDER BY period) AS revenue_change,
       ROUND((total_revenue - LAG(total_revenue) OVER (ORDER BY period)) / NULLIF(LAG(total_revenue) OVER (ORDER BY period), 0) * 100, 2) AS pct_change

FROM tells SQL which table to pull the data from.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS period,
         SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY 1
)
SELECT period,
       total_revenue,
       LAG(total_revenue) OVER (ORDER BY period) AS prev_revenue,
       total_revenue - LAG(total_revenue) OVER (ORDER BY period) AS revenue_change,
       ROUND((total_revenue - LAG(total_revenue) OVER (ORDER BY period)) / NULLIF(LAG(total_revenue) OVER (ORDER BY period), 0) * 100, 2) AS pct_change
FROM monthly

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that calculates the change in revenue vs the previous row using LAG, ordered by order_date.

SELECT *, revenue - LAG(revenue) OVER (ORDER BY order_date) FROM orders

2. Using store_sales, write a query that calculates the change in revenue vs the previous row using LAG, ordered by sale_date.

SELECT *, revenue - LAG(revenue) OVER (ORDER BY sale_date) FROM store_sales

3. Using products, write a query that calculates the change in price vs the previous row using LAG, ordered by category.

SELECT *, price - LAG(price) OVER (ORDER BY category) FROM products

4. Using employees, write a query that calculates the change in salary vs the previous row using LAG, ordered by hire_date.

SELECT *, salary - LAG(salary) OVER (ORDER BY hire_date) FROM employees

5. Using reviews, write a query that calculates the change in rating vs the previous row using LAG, ordered by review_date.

SELECT *, rating - LAG(rating) OVER (ORDER BY review_date) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed