Window function

Also called: OVER (…)

A calculation over a set of rows related to the current row — a running total, a rank, the previous value — that keeps every row instead of collapsing them like GROUP BY.

Formulaf(…) OVER (PARTITION BY … ORDER BY …) — rows stay

How we work it through

1
Decide what each row should be compared with: all rows, or its own customer or month.
2
Put that in PARTITION BY; put the order in ORDER BY.
3
Check the last running value equals the total.

On the practice dataset

The query we run on Meridian, Atterna’s practice marketplace, and what it returns.

SELECT date_trunc('week', created_at) AS week, COUNT(*) AS orders,
  SUM(COUNT(*)) OVER (ORDER BY date_trunc('week', created_at)) AS running
FROM meridian.orders WHERE status = 'paid'
GROUP BY 1 ORDER BY 1;

Resultrunning total 2,625 → 5,376 → 8,665 → 9,071

The common trap

Forgetting PARTITION BY, so the running total or rank covers the whole table instead of each customer or month.

When an AI answers

An AI assistant

“Here is each buyer’s order number, 1 to 9,071.”

What it didn’t know: It forgot PARTITION BY buyer_id, so the numbering ran across the whole table.
Ask back: “Should the numbering restart for each buyer?”

Paste a real AI answer into the checker — it writes these questions for you →