Advanced window functions
Once ROW_NUMBER and running totals feel easy, these are the window techniques that power real dashboards: smoothing noisy numbers, showing shares, bucketing, and subtotals.
Frames and moving averages
A frame limits which rows the window sees. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row moving window.
WITH t AS ( SELECT order_id AS id, SUM(quantity) AS items FROM order_items GROUP BY order_id ) SELECT id, items, AVG(items) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM t ORDER BY id;
FIRST_VALUE and LAST_VALUE
FIRST_VALUE(x) returns x from the first row of the window. Beware LAST_VALUE: with ORDER BY, the default frame ends at the current row, so add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
SELECT name, species, arrived_on,
FIRST_VALUE(name) OVER (PARTITION BY species ORDER BY arrived_on) AS first_in,
LAST_VALUE(name) OVER (PARTITION BY species ORDER BY arrived_on
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_in
FROM pets;Buckets: NTILE and PERCENT_RANK
NTILE(n) splits rows into n near-equal groups (quartiles, deciles). PERCENT_RANK() places each row between 0 and 1.
SELECT name, price, NTILE(3) OVER (ORDER BY price) AS price_band, PERCENT_RANK() OVER (ORDER BY price) AS pct_rank FROM menu_items;
Subtotals with ROLLUP
PostgreSQL's GROUP BY ROLLUP(a, b) adds subtotal rows for each a and a grand total, marked by NULLs. SQLite has no ROLLUP, so stack the grouping levels with UNION ALL.
SELECT category, vegetarian, COUNT(*) AS items FROM menu_items GROUP BY category, vegetarian UNION ALL SELECT category, NULL, COUNT(*) FROM menu_items GROUP BY category UNION ALL SELECT NULL, NULL, COUNT(*) FROM menu_items;
Common mistakes
Integer division in shares (1 / 4 = 0). Multiply by 1.0 first.
LAST_VALUE without an explicit full frame.
Moving averages over days with gaps: ROWS counts rows, not days. Fill the calendar first if days are missing.
Questions
ROWS vs RANGE?+
ROWS counts physical rows. RANGE groups rows with equal ORDER BY values (and, in PostgreSQL, can use value offsets like RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW).
Does SQLite support CUBE or GROUPING SETS?+
No. Combine GROUP BY queries with UNION ALL instead.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.