Orders per Customer as a Window — Window Functions in SQL

The problem

Brightlane's CRM team needs to know how many orders each customer has placed without losing the individual order details.

Write a query to return the ID and customer ID of every order, plus the total number of orders placed by that customer on each row.

Assumptions:

The orders table has one row per order with an id and a customer_id.
A customer's order count is the number of orders linked to that customer_id. The same value should appear on every row that shares a customer_id.

Output:

One row per order, with columns id, customer_id, and customer_order_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,
  customer_id,
  COUNT(*) OVER (
    PARTITION BY
      customer_id
  ) AS customer_order_count
FROM
  orders

The shape

COUNT(*) OVER (PARTITION BY customer_id) counts the rows in each customer's partition and writes that count onto every order belonging to that customer. A customer with three orders sees 3 on each of their three rows; a customer with one order sees 1 on that single row. Every individual order stays in the output.

Clause by clause

SELECT id, customer_id returns each order's identifier and the customer who placed it, one row per order with no collapsing.
The window column is:

COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count

PARTITION BY customer_id splits the row set into one group per distinct customer_id. COUNT(*) runs inside each group independently, so the value attached to a given order is the number of rows in orders that share its customer_id. Every order placed by the same customer sees the same customer_order_count.

FROM orders reads every order. The CRM team wants the count alongside the individual order details, so every row stays in.

Why this and not `GROUP BY customer_id`

GROUP BY customer_id would return one row per customer with the count attached, and the individual order IDs would be lost. The brief is explicit about not losing the individual order details. PARTITION BY runs the same count but preserves every row, replicating the per-customer count across all of that customer's orders.

You practiced COUNT(*) OVER (PARTITION BY ...) — count records within each partition while keeping every individual record visible in the output.

Return the ID and customer ID of every order, plus the total number of orders placed by that customer on each row

The shape

Clause by clause

Why this and not `GROUP BY customer_id`

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

The shape

Clause by clause

Why this and not GROUP BY customer_id

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

Why this and not `GROUP BY customer_id`