Running Count of Orders — Aggregate Window Functions in SQL

The problem

Brightlane's operations team tracks how the cumulative order count grows as orders accumulate by id.

Write a query to return the ID and status of every order, plus the running count of orders from the first record through that order in order of id.

Assumptions:

The orders table has one row per order with an id and a status.
Orders are processed in ascending id order. The running count at each row is the number of orders whose id is less than or equal to that row's id.

Output:

One row per order, with columns id, status, and running_count.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  status,
  COUNT(*) OVER (
    ORDER BY
      id
  ) AS running_count
FROM
  orders

The shape

COUNT(*) OVER (ORDER BY id) attaches a running order count to every row. Each row sees how many orders have been recorded through and including its own id. The ORDER BY id inside OVER is what turns a static total count into a value that grows row by row.

Clause by clause

SELECT id, status returns each order's identifier and its status unchanged. The window count is computed alongside; the original rows stay intact.
COUNT(*) OVER (ORDER BY id) AS running_count counts rows from the start of the table up to and including the current row, in id order. COUNT(*) here counts rows, not a particular column, so every row contributes 1 to the accumulator. The first row carries running_count = 1, the second carries 2, the third carries 3, and so on. Where COUNT(*) without OVER would collapse the table into a single number, the OVER (ORDER BY id) clause keeps every row and grows the count as id advances.
FROM orders reads every order. Every row in the table contributes to the running count.

You practiced COUNT(*) OVER (ORDER BY ...) — running count over an ordered window; each row sees the count of records up to and including itself.

Return the ID and status of every order, plus the running count of orders from the first record through that order in order of `id`

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.