LAG, LEAD and NTILE

LAG, LEAD and NTILE

LAG, LEAD, FIRST_VALUE, and NTILE

LAG(expr [, n [, default]]) reads the value n rows back (1 by default) and LEAD reads forward, returning default or NULL past the edge. FIRST_VALUE, LAST_VALUE and NTH_VALUE(expr, n) read a position in the frame; NTILE(n) deals rows into n buckets. Over a monthly view:

Month-over-month revenue with offsets, the first month and three tiersSQL

CREATE VIEW monthly_revenue AS SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
  SUM(i.quantity * i.unit_price) AS revenue FROM orders AS o JOIN order_items AS i
  ON i.order_id = o.id WHERE o.status <> 'cancelled' GROUP BY month;
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta,
       LEAD(revenue) OVER (ORDER BY month) AS next,
       FIRST_VALUE(revenue) OVER (ORDER BY month) AS first,
       NTILE(3) OVER (ORDER BY revenue DESC) AS tier FROM monthly_revenue ORDER BY month;

Output

Query OK, 0 rows affected (0.014 sec)
+---------+---------+--------+---------+--------+--------+------+
| month   | revenue | prev   | delta   | next   | first  | tier |
+---------+---------+--------+---------+--------+--------+------+
| 2026-03 |  153.37 |   NULL |  153.37 |  49.00 | 153.37 |    1 |
| 2026-04 |   49.00 | 153.37 | -104.37 |  86.50 | 153.37 |    3 |
| 2026-05 |   86.50 |  49.00 |   37.50 | 128.80 | 153.37 |    1 |
| 2026-06 |  128.80 |  86.50 |   42.30 |  50.00 | 153.37 |    1 |
| 2026-07 |   50.00 | 128.80 |  -78.80 |  86.50 | 153.37 |    3 |
| 2026-08 |   86.50 |  50.00 |   36.50 |  53.99 | 153.37 |    2 |
| 2026-09 |   53.99 |  86.50 |  -32.51 |   NULL | 153.37 |    2 |
+---------+---------+--------+---------+--------+--------+------+
7 rows in set (0.001 sec)

The default 0 makes March's delta its whole revenue. Partitioned by customer, LAG gives each customer's gap between orders. NTILE(3) deals seven rows as 3, 2 and 2 and splits the two 86.50 months across tiers, because it counts rows, not values. LAG, LEAD, NTILE and the ranking functions ignore frames; FIRST_VALUE and LAST_VALUE obey them.

MENU