Brightlane's CRM system shows each order alongside a running count of that customer's orders in sequence by id.
Write a query to return the ID, customer ID, and total amount of every order, plus the running count of orders placed by that customer, accumulated in order of id.
Assumptions:
- The
orderstable has one row per order with anid, acustomer_id, and atotal_amount. - Within each customer's orders, the running count at each row is the number of orders for that
customer_idwhoseidis less than or equal to that row'sid. The count restarts at1for each customer's earliest order.
Output:
- One row per order, with columns
id,customer_id,total_amount, andcustomer_running_count.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
customer_id,
total_amount,
COUNT(*) OVER (
PARTITION BY
customer_id
ORDER BY
id
) AS customer_running_count
FROM
ordersThe shape
PARTITION BY customer_id ORDER BY id defines one running window per customer, sequenced by id. COUNT(*) over that window numbers each customer's orders 1, 2, 3, ... in the order they were placed. The counter restarts at every new customer, which is exactly what a per-customer order history looks like in a CRM.
Clause by clause
SELECT id, customer_id, total_amountreturns each order's identifier, the customer who placed it, and its amount. The per-customer running count is attached.COUNT(*) OVER (PARTITION BY customer_id ORDER BY id) AS customer_running_countis the window expression.PARTITION BY customer_idopens a separate window for every distinct customer; the counter restarts at the first row of each customer's window.ORDER BY iddefines the within-window accumulation as every row with the samecustomer_idand a less-than-or-equalid. Customer1's earliest order,id = 1, carriescustomer_running_count = 1. Their next order,id = 63, carries2. Their third,id = 64, carries3. Customer2's earliest order starts the count at1again.FROM ordersreads every order. No filter is needed; the partition handles the per-customer separation.
Why COUNT(*) and not COUNT(customer_id)
COUNT(*) counts rows in the window. COUNT(customer_id) counts non-NULL values of customer_id in the window. On a table where customer_id is never NULL, both produce the same number. The * form is the canonical spelling because the question is "how many orders has this customer placed so far," which is a row count by definition. Reserve COUNT(column) for the cases where NULL in that column means "do not count this row."
You practiced COUNT(*) OVER (PARTITION BY ... ORDER BY ...) — per-partition running count; each partition's first record sees 1, the second sees 2, and so on.
