Window functions

A normal aggregate (SUM, AVG, COUNT) collapses many rows into one. A window function computes an aggregate over a “window” of rows without collapsing — every input row gets one output row.

The syntax adds OVER (...) after the function:

SUM(amount) OVER (PARTITION BY user_id ORDER BY date)

Three pieces:

PARTITION BY — split the rows into independent groups (like GROUP BY, but the rows stay).
ORDER BY — order rows within each partition.
The frame — optional; defines which rows around the current row to include.

Try it

The seed schema has orders joined to users and products. Let’s compute the running total revenue per country:

▶ SQL · SQLite

Ready · ⌘↵ runs

-- Running total revenue per country, ordered by date.
SELECT
u.country,
o.order_date,
p.price * o.qty                                       AS revenue,
SUM(p.price * o.qty) OVER (
  PARTITION BY u.country
  ORDER BY o.order_date
)                                                     AS running_total
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
ORDER BY u.country, o.order_date;

Result

(click Run)

The same SUM you’ve used before — now scoped to each country and running through time. No GROUP BY needed, and you get one row per order.

Rankings — ROW_NUMBER, RANK, DENSE_RANK

▶ SQL · SQLite

Ready · ⌘↵ runs

-- Rank users within each country by total spending.
SELECT
u.country,
u.name,
SUM(p.price * o.qty)                                  AS spend,
RANK()       OVER (PARTITION BY u.country ORDER BY SUM(p.price * o.qty) DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY u.country ORDER BY SUM(p.price * o.qty) DESC) AS drk,
ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY SUM(p.price * o.qty) DESC) AS rn
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY u.country, u.name
ORDER BY u.country, rk;

Result

(click Run)

The differences:

Function	What happens on ties
`ROW_NUMBER`	Each row gets a unique sequential number (ties broken arbitrarily).
`RANK`	Tied rows get the same rank; the next rank skips ahead (1, 2, 2, 4).
`DENSE_RANK`	Tied rows get the same rank; next rank doesn’t skip (1, 2, 2, 3).

Use ROW_NUMBER when you want to deduplicate. Use RANK / DENSE_RANK when ties should share a position.

LAG and LEAD — the “previous row” superpower

▶ SQL · SQLite

Ready · ⌘↵ runs

-- For each order, show the user's previous order date and the gap.
SELECT
u.name,
o.order_date,
LAG(o.order_date) OVER (
  PARTITION BY u.id ORDER BY o.order_date
)                                                     AS prev_order,
CAST(julianday(o.order_date)
   - julianday(LAG(o.order_date) OVER (
       PARTITION BY u.id ORDER BY o.order_date)) AS INT)
                                                      AS days_since_last
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY u.name, o.order_date;

Result

(click Run)

LAG(col) gives the value of col from the previous row (within the partition, in order). LEAD(col) gives it from the next row. Combine with date math and you get day-over-day deltas, gap analysis, churn windows — the bread and butter of analytics.

Top-N per group, the full pattern

▶ SQL · SQLite

Ready · ⌘↵ runs

-- Top 2 most recent orders per user.
SELECT * FROM (
SELECT
  u.name,
  o.order_date,
  p.name AS product,
  ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.order_date DESC) AS rn
FROM orders o
JOIN users u    ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
) t
WHERE rn <= 2
ORDER BY name, rn;

Result

(click Run)

The reason for the outer query: window functions are evaluated after the WHERE clause of the same SELECT, so you can’t filter on them inline. Wrap-and-filter is the idiom.

Quick check

✦ Quick check

0/3 answered

Q1.What's the difference between a window function and an aggregate function?

Q2.Three users tied for the top score. Which function gives them ranks (1, 1, 1, **4**)?

Q3.You want the top 3 customers per region. What's the cleanest approach?

What you'll learn

Try it

Rankings — ROW_NUMBER, RANK, DENSE_RANK

LAG and LEAD — the “previous row” superpower

Top-N per group, the full pattern

Quick check

✦ Quick check

Finished the lesson?