Brightlane's audit team tracks cumulative revenue within each order status group as orders are processed in order of their ID.
Write a query to return the ID, status, and amount of every order, plus the running total of amounts within the order's status group up to and including that order on each row.
Assumptions:
- The
orderstable has one row per order with anid, astatus, and atotal_amount. - A row's running total is the combined
total_amountacross every order with the samestatuswhoseidis less than or equal to that row'sid. - The first order in a status group sees its own
total_amountas the running total. Each subsequent order in that status group sees the cumulative sum up to and including itself.
Output:
- One row per order, with columns
id,status,total_amount, andstatus_running_total.
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,
status,
total_amount,
SUM(total_amount) OVER (
PARTITION BY
status
ORDER BY
id
) AS status_running_total
FROM
ordersThe shape
Adding ORDER BY id inside OVER converts the window from a partition-wide total into a running total. SUM(total_amount) OVER (PARTITION BY status ORDER BY id) returns, on each row, the cumulative sum of total_amount across every order with the same status whose id is less than or equal to the current row's id. The audit team gets a separate running total per status group, each restarting at the lowest id in its group.
Clause by clause
SELECT id, status, total_amountreturns each order's identifier, status, and individual amount, one row per order.- The window column is:
SUM(total_amount) OVER (PARTITION BY status ORDER BY id) AS status_running_totalPARTITION BY status splits the row set by status. Inside each status partition, ORDER BY id sequences the rows by ascending id. With both pieces in place, SUM(total_amount) no longer ranges over the whole partition. It ranges from the first row of the partition up to and including the current row. The first order in each status group sees only its own total_amount; the second sees its own plus the first; and so on.
FROM ordersreads every order. The running total is computed for every row in every status group.
Why this and not SUM(total_amount) OVER (PARTITION BY status) from M1
The M1 form, with no ORDER BY inside OVER, returned the partition's complete total on every row, the same value duplicated across all rows that shared a status. Adding ORDER BY id to the same window changes what SUM ranges over: it now accumulates row by row in id order within the partition, instead of summing the whole partition at once. Same data, same partition, different window definition, different number on each row.
The trap
ORDER BY inside OVER is not the same as ORDER BY at the end of the query. The ORDER BY id here is part of the window definition and only affects how SUM accumulates inside each partition. It does not guarantee that the final output rows come out sorted by id. If the audit team needs the report itself ordered by id, that requires a separate ORDER BY id after FROM orders. The two ORDER BY clauses serve different jobs and have to be written separately when both are wanted.
You practiced SUM(...) OVER (PARTITION BY x ORDER BY y) — adding ORDER BY inside the window converts the computation from a partition-wide total to a running total within the partition.
