Window Functions: LAG, LEAD, and NTILE

Offset functions read values from other rows in the window — perfect for "compared with the previous one" questions.

LAG and LEAD

-- standalone
CREATE TABLE monthly_revenue (month TEXT, revenue INTEGER);
INSERT INTO monthly_revenue VALUES
  ('2025-01', 12000), ('2025-02', 13500), ('2025-03', 12800), ('2025-04', 15600);

SELECT month, revenue,
  LAG(revenue)  OVER (ORDER BY month) AS previous,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change,
  LEAD(revenue) OVER (ORDER BY month) AS next
FROM monthly_revenue
ORDER BY month;

+---------+---------+----------+--------+------------+-------+
|  month  | revenue | previous | change | pct_change | next  |
+---------+---------+----------+--------+------------+-------+
| 2025-01 |   12000 | NULL     | NULL   | NULL       | 13500 |
| 2025-02 |   13500 |    12000 |   1500 |       12.5 | 12800 |
| 2025-03 |   12800 |    13500 |   -700 |       -5.2 | 15600 |
| 2025-04 |   15600 |    12800 |   2800 |       21.9 | NULL  |
+---------+---------+----------+--------+------------+-------+

• LAG(col) — the value from the previous row; LAG(col, 2) from two rows back.
• LEAD(col) — the value from the next row.
• At the edges there's no previous or next row, so the result is NULL (or a default: LAG(revenue, 1, 0)).

Time between events

SELECT customer_id, id AS order_id, ordered_on,
  JULIANDAY(ordered_on) - JULIANDAY(LAG(ordered_on) OVER (
    PARTITION BY customer_id ORDER BY ordered_on
  )) AS days_since_previous
FROM orders
WHERE customer_id IN (1, 3)
ORDER BY customer_id, ordered_on;

+-------------+----------+------------+---------------------+
| customer_id | order_id | ordered_on | days_since_previous |
+-------------+----------+------------+---------------------+
|           1 |        1 | 2025-01-05 | NULL                |
|           1 |        2 | 2025-02-14 |                40.0 |
|           1 |        8 | 2025-04-22 |                67.0 |
|           3 |        4 | 2025-03-01 | NULL                |
|           3 |        5 | 2025-03-18 |                17.0 |
+-------------+----------+------------+---------------------+

With PARTITION BY customer_id, each customer's first order has no previous order.

FIRST_VALUE and NTILE

SELECT name, salary,
  FIRST_VALUE(name) OVER (ORDER BY salary DESC) AS top_earner,
  NTILE(3) OVER (ORDER BY salary DESC)          AS salary_band
FROM employees
ORDER BY salary DESC;

+-------+--------+------------+-------------+
| name  | salary | top_earner | salary_band |
+-------+--------+------------+-------------+
| Sofia | 120000 | Sofia      |           1 |
| Priya |  98000 | Sofia      |           1 |
| Omar  |  95000 | Sofia      |           2 |
| Chen  |  70000 | Sofia      |           2 |
| Lucia |  64000 | Sofia      |           3 |
| Kofi  |  52000 | Sofia      |           3 |
+-------+--------+------------+-------------+

• FIRST_VALUE — the first value in the window.
• NTILE(n) — splits rows into n roughly equal buckets: here, top, middle, and bottom thirds by salary. Quartiles are NTILE(4).

Named windows

When several functions share a window, name it once:

SELECT month, revenue,
  LAG(revenue) OVER w  AS previous,
  LEAD(revenue) OVER w AS next
FROM (SELECT '2025-01' AS month, 100 AS revenue UNION ALL SELECT '2025-02', 120)
WINDOW w AS (ORDER BY month);

+---------+---------+----------+------+
|  month  | revenue | previous | next |
+---------+---------+----------+------+
| 2025-01 |     100 | NULL     |  120 |
| 2025-02 |     120 |      100 | NULL |
+---------+---------+----------+------+