Window functions

01The problem

Two requests land on your desk the same morning:

1. The sales director wants customers ranked by containers shipped in 2026, with their rank shown next to their name.
2. Finance wants each month's revenue alongside a running total for the year so far.

GROUP BY can total things, but it collapses rows. Here you need a calculation across rows and to keep every row.

02The concept

A window function calculates a value for each row using a set of related rows (its "window"), without collapsing them.

You recognise one by OVER (…):

function() OVER (PARTITION BY … ORDER BY …)

• PARTITION BY splits rows into groups; the function restarts in each group. Leave it out and the whole result is one group.
• ORDER BY inside OVER sets the order the function works through the rows.

Common window functions:

03Example

WITH totals AS (
  SELECT c.company_name, SUM(s.containers) AS containers
  FROM shipments AS s
  JOIN customers AS c ON c.customer_id = s.customer_id
  WHERE s.booking_date >= '2026-01-01'
  GROUP BY c.customer_id, c.company_name
)
SELECT
  company_name,
  containers,
  RANK() OVER (ORDER BY containers DESC) AS container_rank
FROM totals
ORDER BY container_rank
LIMIT 15;

04Walkthrough

• The CTE totals 2026 containers per customer, using what you learned in the last few lessons.
• RANK() OVER (ORDER BY containers DESC) looks at all rows, orders them by containers, and gives each one its position.
• Every customer row is still there. The rank is simply a new column.

Now a running total. First total each month, then add SUM(…) OVER (ORDER BY month):

WITH monthly AS (
  SELECT strftime('%Y-%m', payment_date) AS month, SUM(amount) AS received
  FROM payments
  WHERE payment_date >= '2026-01-01'
  GROUP BY month
)
SELECT
  month,
  received,
  SUM(received) OVER (ORDER BY month) AS received_year_to_date
FROM monthly
ORDER BY month;

For each month, the running total adds up every month up to and including that one.

PARTITION BY restarts the calculation per group. This numbers each customer's shipments from newest to oldest, so rn = 1 is their most recent booking:

SELECT customer_id, shipment_id, booking_date
FROM (
  SELECT
    customer_id,
    shipment_id,
    booking_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY booking_date DESC, shipment_id DESC) AS rn
  FROM shipments
) AS numbered
WHERE rn = 1
ORDER BY booking_date;

The customers at the top of this list are the ones who haven't booked for longest. Sales teams use exactly this kind of query to spot customers drifting away.

05Practice

06Challenge

07Check your understanding