CTEs and Window Functions · Lesson 21 of 30

SQL LAG, LEAD, Running Totals and Moving Averages

6 min read Updated Nov 7, 2026 Saurabh Samir

On this page

LAG and LEAD
Running totals
Window frames
Percent of total, FIRST_VALUE and NTILE
Practice
Summary

Time-based analysis is where window functions shine: month-over-month growth, cumulative revenue, 3-day moving averages, each customer's gap between orders. LAG and LEAD look at neighbouring rows, and window frames control exactly which rows a running calculation includes.

In this lesson

LAG and LEAD: previous and next rows
Running totals
Window frames and moving averages
Percent of total, FIRST_VALUE and NTILE

LAG and LEAD

WITH monthly AS (
  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,
         SUM(i.quantity * i.unit_price)     AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month)              AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month)    AS change_amt,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1)  AS change_pct
FROM monthly
ORDER BY month;

Output

+---------+---------+------------+------------+------------+
| month   | revenue | prev_month | change_amt | change_pct |
+---------+---------+------------+------------+------------+
| 2025-08 | 4138.00 |       NULL |       NULL |       NULL |
| 2025-09 | 4697.00 |    4138.00 |     559.00 |       13.5 |
| 2025-10 | 2738.00 |    4697.00 |   -1959.00 |      -41.7 |
| 2025-11 | 3798.00 |    2738.00 |    1060.00 |       38.7 |
+---------+---------+------------+------------+------------+

LAG(col) returns the value from the previous row in the window order (NULL for the first row); LEAD(col) returns the next row's value. Both accept an offset and a default: LAG(revenue, 1, 0).

Days between a customer's orders

SELECT customer_id,
       order_id,
       order_date,
       LAG(order_date) OVER w                          AS previous_order,
       DATEDIFF(order_date, LAG(order_date) OVER w)    AS days_since_previous
FROM orders
WHERE customer_id IN (1, 4)
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;

Output

+-------------+----------+------------+----------------+---------------------+
| customer_id | order_id | order_date | previous_order | days_since_previous |
+-------------+----------+------------+----------------+---------------------+
|           1 |      101 | 2025-08-02 | NULL           |                NULL |
|           1 |      103 | 2025-08-19 | 2025-08-02     |                  17 |
|           1 |      109 | 2025-10-21 | 2025-08-19     |                  63 |
|           4 |      105 | 2025-09-07 | NULL           |                NULL |
|           4 |      111 | 2025-11-05 | 2025-09-07     |                  59 |
+-------------+----------+------------+----------------+---------------------+

The WINDOW w AS (…) clause names a window definition so you don't repeat it.

Running totals

WITH daily AS (
  SELECT o.order_date, SUM(i.quantity * i.unit_price) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.order_date
)
SELECT order_date,
       revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM daily
ORDER BY order_date;

Output

+------------+---------+---------------+
| order_date | revenue | running_total |
+------------+---------+---------------+
| 2025-08-02 |  839.00 |        839.00 |
| 2025-08-05 | 2499.00 |       3338.00 |
| 2025-08-19 |  800.00 |       4138.00 |
| 2025-09-07 | 3697.00 |       7835.00 |
| 2025-10-03 | 1299.00 |       9134.00 |
| 2025-11-02 |  700.00 |       9834.00 |
| 2025-11-06 | 1000.00 |      10834.00 |
+------------+---------+---------------+

With an ORDER BY inside OVER, SUM adds up every row from the start of the partition to the current row. Add PARTITION BY customer_id to restart the running total for each customer.

Window frames

The frame says which rows around the current one are included:

Common frames

UNBOUNDED PRECEDING → CURRENT ROWrunning total
2 PRECEDING → CURRENT ROW3-row moving window
1 PRECEDING → 1 FOLLOWINGcentred window
UNBOUNDED PRECEDING → UNBOUNDED FOLLOWINGwhole partition

SELECT order_id,
       total,
       ROUND(AVG(total) OVER (ORDER BY order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3,
       SUM(total) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS running
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
) t
ORDER BY order_id;

Output

+----------+---------+--------------+----------+
| order_id | total   | moving_avg_3 | running  |
+----------+---------+--------------+----------+
|      101 |  839.00 |       839.00 |   839.00 |
|      102 | 2499.00 |      1669.00 |  3338.00 |
|      103 |  800.00 |      1379.33 |  4138.00 |
|      104 | 1499.00 |      1599.33 |  5637.00 |
|      105 | 3697.00 |      1998.67 |  9334.00 |
|      106 | 1000.00 |      2065.33 | 10334.00 |
|      107 | 1299.00 |      1998.67 | 11633.00 |
|      108 |  899.00 |      1066.00 | 12532.00 |
|      109 |  540.00 |       912.67 | 13072.00 |
|      110 |  700.00 |       713.00 | 13772.00 |
|      111 | 2098.00 |      1112.67 | 15870.00 |
|      112 | 1000.00 |      1266.00 | 16870.00 |
+----------+---------+--------------+----------+

ROWS vs RANGE: the duplicate-value surprise

When you write only ORDER BY inside OVER, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step, so tied rows all get the same running total. Write ROWS BETWEEN … explicitly when you want strictly row-by-row behaviour.

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary DESC)                                         AS range_default,
       SUM(salary) OVER (ORDER BY salary DESC, emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC, emp_id;

Output

+--------------+--------+---------------+------------+
| name         | salary | range_default | rows_frame |
+--------------+--------+---------------+------------+
| Rohit Mehta  | 180000 |        180000 |     180000 |
| Sana Iqbal   | 120000 |        420000 |     300000 |
| Dev Patel    | 120000 |        420000 |     420000 |
| Priya Sharma |  95000 |        515000 |     515000 |
+--------------+--------+---------------+------------+

The two tied salaries share 420,000 in the RANGE column, because RANGE adds all rows with the same salary in one step. The ROWS column adds them one at a time (with emp_id deciding who goes first), which is usually what a "running total" should do.

Percent of total, FIRST_VALUE and NTILE

SELECT name,
       department,
       salary,
       ROUND(100 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) AS top_earner,
       NTILE(3) OVER (ORDER BY salary DESC, name)                           AS pay_band
FROM employees
WHERE department IN ('Engineering', 'Sales')
ORDER BY department, salary DESC, name;

Output

+--------------+-------------+--------+-------------+-------------+----------+
| name         | department  | salary | pct_of_dept | top_earner  | pay_band |
+--------------+-------------+--------+-------------+-------------+----------+
| Rohit Mehta  | Engineering | 180000 |        35.0 | Rohit Mehta |        1 |
| Dev Patel    | Engineering | 120000 |        23.3 | Rohit Mehta |        1 |
| Sana Iqbal   | Engineering | 120000 |        23.3 | Rohit Mehta |        2 |
| Priya Sharma | Engineering |  95000 |        18.4 | Rohit Mehta |        2 |
| Farhan Ali   | Sales       | 140000 |        46.2 | Farhan Ali  |        1 |
| Lata Joshi   | Sales       |  85000 |        28.1 | Farhan Ali  |        3 |
| Tom Mathew   | Sales       |  78000 |        25.7 | Farhan Ali  |        3 |
+--------------+-------------+--------+-------------+-------------+----------+

NTILE(n) splits rows into n roughly equal buckets – useful for quartiles and "top third of customers". LAST_VALUE needs a frame ending at UNBOUNDED FOLLOWING to see the true last row – another frame gotcha.

Practice

Try it yourself

For each employee, show the salary of the next-lower-paid person in the same department.

Show solution

SELECT department, name, salary,
       LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC, emp_id) AS next_lower
FROM employees
WHERE department IN ('Sales', 'HR')
ORDER BY department, salary DESC;

Output

+------------+--------------+--------+------------+
| department | name         | salary | next_lower |
+------------+--------------+--------+------------+
| HR         | Kavya Reddy  |  90000 |      65000 |
| HR         | Imran Shaikh |  65000 |       NULL |
| Sales      | Farhan Ali   | 140000 |      85000 |
| Sales      | Lata Joshi   |  85000 |      78000 |
| Sales      | Tom Mathew   |  78000 |       NULL |
+------------+--------------+--------+------------+

Show each customer's orders with a running count of their orders.

Show solution

SELECT customer_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS nth_order
FROM orders
WHERE customer_id IN (1, 2)
ORDER BY customer_id, order_date;

Output

+-------------+----------+------------+-----------+
| customer_id | order_id | order_date | nth_order |
+-------------+----------+------------+-----------+
|           1 |      101 | 2025-08-02 |         1 |
|           1 |      103 | 2025-08-19 |         2 |
|           1 |      109 | 2025-10-21 |         3 |
|           2 |      102 | 2025-08-05 |         1 |
|           2 |      107 | 2025-10-03 |         2 |
+-------------+----------+------------+-----------+

Quick check

What does LAG return for the first row?

NULL, or the default you pass as the third argument.

How do you restart a running total per customer?

Add PARTITION BY customer_id inside OVER.

Which frame gives a 7-day moving average over daily rows?

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Summary

LAG/LEAD read previous/next rows – ideal for growth and gaps.
SUM() OVER (ORDER BY …) gives running totals; frames (ROWS BETWEEN) give moving windows.
Default frames use RANGE (ties grouped); percent of total and NTILE round out the toolkit.

Interview practice: advanced SQL coding questions.

Finished this lesson?

Mark it complete to track your progress, then carry on with the next lesson.

Next lesson