Running Average Price — Aggregate Window Functions in SQL

The problem

Brightlane's pricing analyst tracks how the average product price changes as products are added to the catalog in sequence by id.

Write a query to return the ID, name, and price of every product, plus the running average price accumulated from the first product through that product in order of id.

Assumptions:

The products table has one row per product with an id, a name, and a price.
Products are reviewed in ascending id order. The running average at each row is the average of price across every product whose id is less than or equal to that row's id.

Output:

One row per product, with columns id, name, price, and running_avg_price.

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,
  name,
  price,
  AVG(price) OVER (
    ORDER BY
      id
  ) AS running_avg_price
FROM
  products

The shape

AVG(price) OVER (ORDER BY id) recomputes the mean as each product enters the catalog in id order. Where a plain AVG(price) would return one number for the whole table, the windowed form attaches a fresh average to every row, computed from every product seen so far.

Clause by clause

SELECT id, name, price returns each product's identifier, name, and price unchanged. The running average is added alongside; the rows are preserved.
AVG(price) OVER (ORDER BY id) AS running_avg_price averages price across every row from the first product through the current one. The ORDER BY id inside OVER defines that growing set. On the first row, the average is the single price itself, 999. On the second row, the average is over two prices, (999 + 1199) / 2 = 1099. As more rows enter the running set, the average shifts toward the overall catalog mean.
FROM products reads every product. Every row in the catalog contributes once it enters the running set.

You practiced AVG(...) OVER (ORDER BY ...) — running average; the value updates as the cumulative set grows row by row.

Return the ID, name, and price of every product, plus the running average price accumulated from the first product through that product 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.