Module 4: CTEs, Views and Window Functions
Window Functions: LEAD, LAG and Frames
Compare a row with its neighbours using LAG and LEAD, build running totals and moving averages with frame clauses, and avoid the default-frame trap behind LAST_VALUE.
Ranking looks at where a row sits in its group. The second family of window functions looks at the rows around it: the previous month, the next order, the last three days. Time-series reporting, growth rates, running totals and "days since last purchase" all live here.
Two tools do the work. Offset functions (LAG, LEAD) reach to a specific neighbouring row. Frames (ROWS BETWEEN ...) define a sliding set of rows to aggregate over.
LAG and LEAD#
LAG(x) returns x from the previous row in the window's order. LEAD(x) returns it from the next row.
Start with monthly revenue from the earlier lessons:
WITH monthly AS (
SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY 1
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly
ORDER BY month;
month | revenue | prev_month | next_month
---------+---------+------------+------------
2024-02 | 439.00 | | 501.50
2024-03 | 501.50 | 439.00 | 1229.50
2024-04 | 1229.50 | 501.50 | 710.00
2024-05 | 710.00 | 1229.50 | 259.50
2024-06 | 259.50 | 710.00 |
The first row has no previous month and the last has no next, so both come back as NULL. Both functions take optional arguments: LAG(revenue, 2) looks two rows back, and LAG(revenue, 1, 0) supplies 0 instead of NULL when there is no row.
Month-over-month change#
SELECT month, revenue,
revenue - LAG(revenue) OVER w AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER w)
/ LAG(revenue) OVER w, 1) AS pct_change
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month;
month | revenue | change | pct_change
---------+---------+----------+------------
2024-02 | 439.00 | |
2024-03 | 501.50 | 62.50 | 14.2
2024-04 | 1229.50 | 728.00 | 145.2
2024-05 | 710.00 | -519.50 | -42.3
2024-06 | 259.50 | -450.50 | -63.5
The WINDOW w AS (...) clause names a window definition once and reuses it. Cleaner, and it makes it obvious that all three expressions share one sort.
Per entity: time since the last order#
Add PARTITION BY and the offset resets for each customer:
SELECT customer_id, order_id, order_date,
order_date - LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS days_since_prev
FROM orders
WHERE customer_id IN (1, 2, 3, 6)
ORDER BY customer_id, order_date;
customer_id | order_id | order_date | days_since_prev
-------------+----------+------------+-----------------
1 | 1 | 2024-02-01 |
1 | 2 | 2024-03-15 | 43
1 | 10 | 2024-05-02 | 48
2 | 3 | 2024-02-10 |
2 | 7 | 2024-04-05 | 55
3 | 4 | 2024-02-20 |
3 | 13 | 2024-06-03 | 104
6 | 8 | 2024-04-11 |
6 | 12 | 2024-05-21 | 40
Subtracting two DATE values in PostgreSQL gives a whole number of days. Each customer's first order has no predecessor, so NULL. Average these gaps and you have a repeat-purchase interval.
Running totals and frames#
An aggregate becomes a window function the moment you add OVER. With an ORDER BY inside it, the aggregate accumulates row by row.
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month;
month | revenue | running_total
---------+---------+---------------
2024-02 | 439.00 | 439.00
2024-03 | 501.50 | 940.50
2024-04 | 1229.50 | 2170.00
2024-05 | 710.00 | 2880.00
2024-06 | 259.50 | 3139.50
What is happening is that every row has a frame: the subset of the partition the aggregate looks at for that row. When you give an ORDER BY and nothing else, the frame runs from the first row of the partition to the current row. That is why the total grows.
You can set the frame yourself.
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
That averages the current month and the one before it, a moving average:
month | revenue | avg_last_2_months
---------+---------+-------------------
2024-02 | 439.00 | 439.00
2024-03 | 501.50 | 470.25
2024-04 | 1229.50 | 865.50
2024-05 | 710.00 | 969.75
2024-06 | 259.50 | 484.75
The first row has only itself in its frame, so its "average" is its own value. Mention that in any report where the opening rows matter.
Frame boundaries are built from five pieces: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.
rows in partition: r1 r2 r3 [r4] r5 r6 r7
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -> frame for r4: r2 r3 r4
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -> frame for r4: r3 r4 r5
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -> r1 .. r4
ROWS versus RANGE#
ROWS counts physical rows. RANGE works on the values of the ORDER BY column and includes every peer, meaning every row with the same ordering value.
This matters, because RANGE is the default frame when you write ORDER BY without a frame clause. Ties then get lumped together. Take Engineering, where two people earn 120000:
SELECT full_name, salary,
SUM(salary) OVER (ORDER BY salary) AS range_sum,
SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) AS rows_sum
FROM employees
WHERE department_id = 1
ORDER BY salary, employee_id;
full_name | salary | range_sum | rows_sum
----------------+--------+-----------+----------
Sofia Martins | 120000 | 240000 | 120000
Kenji Watanabe | 120000 | 240000 | 240000
Arjun Rao | 140000 | 380000 | 380000
Meera Iyer | 185000 | 565000 | 565000
With RANGE, both tied rows see the same frame, which includes both of them, so each reports 240000. With ROWS, the running total steps up one row at a time. Which tied row gets the lower rows_sum is not guaranteed unless you add a tiebreaker to the window's ORDER BY.
If you want a strict running total, write ROWS explicitly. It is also faster. PostgreSQL 11 added GROUPS as a third mode that counts peer groups.
The LAST_VALUE trap#
FIRST_VALUE, LAST_VALUE and NTH_VALUE return a value from a specific position in the frame. LAST_VALUE has caught nearly everyone once, because of the default frame.
SELECT full_name, salary,
FIRST_VALUE(full_name) OVER (ORDER BY salary DESC) AS top_earner,
LAST_VALUE(full_name) OVER (ORDER BY salary DESC) AS last_default,
LAST_VALUE(full_name) OVER (
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_full
FROM employees
WHERE department_id = 2
ORDER BY salary DESC;
full_name | salary | top_earner | last_default | last_full
---------------+--------+------------+----------------+---------------
Priya Nair | 130000 | Priya Nair | Priya Nair | Daniel Okafor
Lena Fischer | 95000 | Priya Nair | Lena Fischer | Daniel Okafor
Daniel Okafor | 90000 | Priya Nair | Daniel Okafor | Daniel Okafor
last_default is just the current row each time. The default frame ends at CURRENT ROW, so the "last" row in the frame is the row itself. To see the true last row of the partition, widen the frame to UNBOUNDED FOLLOWING.
Percent of total with a window over an aggregate#
You can run a window function over the results of a GROUP BY, since window functions run after grouping. Category share of revenue:
SELECT p.category,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER (), 1) AS pct
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;
category | revenue | pct
-------------+---------+------
Displays | 867.00 | 27.6
Furniture | 848.00 | 27.0
Accessories | 823.50 | 26.2
Audio | 398.00 | 12.7
Office | 144.00 | 4.6
Video | 59.00 | 1.9
The inner SUM is the aggregate for each group. The outer SUM(...) OVER () is a window function that adds up those six group totals, with an empty OVER meaning one window over everything. Two sums, two different levels.
Common mistakes#
Using the default frame with LAST_VALUE. Spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Running totals over RANGE with duplicate order values. Tied rows jump together. Use ROWS, or add a unique tiebreaker.
Forgetting PARTITION BY on per-entity offsets. LAG(order_date) without it compares customer 2's first order with customer 1's last one.
Not ordering the window. LAG without an ORDER BY inside OVER has no defined previous row.
Computing the same window in five places. Use a named WINDOW clause.
Practice#
- For every order, show the customer's cumulative spend so far (running total of order totals per customer, non-cancelled only). Aisha Khan's third order should show 726.00.
- Compute the 3-month moving average of monthly revenue using
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. What does April show? (Answer: 723.33.) - Using
LEAD, list each customer's order with the date of their next order, then keep only rows where the next order is more than 45 days away. - Write the month-over-month change query using a named
WINDOWclause, and add a column flagging months where revenue fell. - Explain, in your own words, why
SUM(x) OVER (ORDER BY d)andSUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)differ when two rows share the samed.