SQL Intermediate Lesson 4: LAG and LEAD – Month-on-Month Growth

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 4 of 10

LAG looks at the previous row, LEAD at the next one, within the window’s order.

WITH m AS (SELECT month, SUM(amount) AS total FROM sales GROUP BY month)
SELECT month, total,
       LAG(total) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * (total - LAG(total) OVER (ORDER BY month))
             / LAG(total) OVER (ORDER BY month), 1) AS growth_pct
FROM m;

The first month has no previous month, so prev and growth are NULL. That’s correct, not an error.

Gaps between orders

SELECT order_id, order_date,
       julianday(order_date) - julianday(LAG(order_date) OVER (ORDER BY order_date)) AS gap_days
FROM orders WHERE customer_id = 1;

Date maths differs by database: SQLite julianday(a) - julianday(b), PostgreSQL a - b, SQL Server DATEDIFF(day, b, a), MySQL DATEDIFF(a, b).

Common mistakes

• Dividing by a zero previous month: wrap the divisor in NULLIF(..., 0).
• Missing months: LAG takes the previous row, not the previous month. Fill gaps with a calendar (lesson 7) first.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.