sqlstreak

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;

Share of total

SUM(x) OVER () is the grand total on every row, so dividing gives each row's share. After GROUP BY, nest it: SUM(SUM(x)) OVER ().

SELECT species, COUNT(*) AS pets,
  1.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share
FROM pets
GROUP BY species;

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.

Practise it now