SQL LAG() and LEAD(): Compare a Row With the Previous or Next

Start with revenue per month for the second half of 2024, then add two columns: the previous month's revenue with LAG and the next month's with LEAD.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2024-07-01'
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS previous_month,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

Each row now carries its neighbours' values. The first month has no previous row, so previous_month is NULL; the last month has no next row, so next_month is NULL.

LAG(column [, offset [, default]])  OVER ([PARTITION BY group_column] ORDER BY sort_column)
LEAD(column [, offset [, default]]) OVER ([PARTITION BY group_column] ORDER BY sort_column)

The ORDER BY inside OVER defines what "previous" and "next" mean. LAG and LEAD are window functions, available in PostgreSQL, SQL Server, Oracle, MySQL 8.0+ and SQLite 3.25+.

Once the previous value is on the same row, the change is ordinary arithmetic. This calculates the difference and the percentage change from the month before:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2024-07-01'
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS change_pct
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

The first row shows NULL for both calculations, because any arithmetic with NULL gives NULL. That is the correct answer: there is no earlier month to compare with.

With PARTITION BY, "previous" means the previous row for the same customer. This shows each order next to that customer's previous order date and the number of days between them:

SELECT user_id, order_date,
       LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date, id) AS previous_order,
       CAST(julianday(order_date)
            - julianday(LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date, id)) AS INTEGER) AS days_since_previous
FROM orders
WHERE user_id IN (37, 77)
ORDER BY user_id, order_date, id;

▶ Run this query in the SQL Playground

A customer's first order has no previous order, so both extra columns are NULL, and the comparison never reaches across to another customer. (julianday is SQLite's way of subtracting dates; other databases use DATEDIFF or plain subtraction.)

The second argument reaches further back, and the third replaces the NULL when no row exists. Here LAG(revenue, 2, 0) looks two months back and returns 0 when there is nothing there:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2024-07-01'
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       LAG(revenue, 2, 0) OVER (ORDER BY month) AS two_months_ago
FROM monthly
ORDER BY month;

▶ Run this query in the SQL Playground

With monthly data, an offset of 12 compares each month with the same month a year earlier.

Three errors and how to avoid them.

LAG(order_date) OVER (ORDER BY order_date)

LAG(order_date) OVER (
  PARTITION BY user_id
  ORDER BY order_date
)

revenue - LAG(revenue, 1, 0) OVER (ORDER BY month)

revenue - LAG(revenue) OVER (ORDER BY month)

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly
WHERE month = '2024-12';

WITH with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly
)
SELECT month, revenue, prev
FROM with_prev
WHERE month = '2024-12';

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show monthly order counts for 2024 with the previous month's count beside each. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH monthly AS (
     SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
     FROM orders
     WHERE order_date >= '2024-01-01'
     GROUP BY strftime('%Y-%m', order_date)
   )
   SELECT month, orders,
          LAG(orders) OVER (ORDER BY month) AS previous_month
   FROM monthly
   ORDER BY month;

   ▶ Run this query in the SQL Playground

   One row per month, 12 in total; the first has NULL because nothing comes before it.

2. 2Medium For customers 37, 54 and 77, show each order with the date of that customer's next order. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT user_id, order_date,
          LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date, id) AS next_order
   FROM orders
   WHERE user_id IN (37, 54, 77)
   ORDER BY user_id, order_date, id;

   ▶ Run this query in the SQL Playground

   LEAD looks forward. Each customer's last order has NULL in next_order.

3. 3Medium Show yearly revenue and the change from the previous year. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH yearly AS (
     SELECT strftime('%Y', order_date) AS year,
            ROUND(SUM(total), 2) AS revenue
     FROM orders
     GROUP BY strftime('%Y', order_date)
   )
   SELECT year, revenue,
          ROUND(revenue - LAG(revenue) OVER (ORDER BY year), 2) AS change
   FROM yearly
   ORDER BY year;

   ▶ Run this query in the SQL Playground

   The same pattern as month-over-month, grouped by year instead.

4. 4Challenging Find the five longest gaps, in days, between two consecutive orders of the same customer. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH gaps AS (
     SELECT user_id, order_date,
            LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date, id) AS previous_order
     FROM orders
   )
   SELECT user_id, previous_order, order_date,
          CAST(julianday(order_date) - julianday(previous_order) AS INTEGER) AS gap_days
   FROM gaps
   WHERE previous_order IS NOT NULL
   ORDER BY gap_days DESC, user_id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   LAG is calculated in the CTE, then the outer query removes first orders and sorts by the size of the gap.