Brightlane's revenue dashboard tracks the running total of order amounts within each status, accumulating in order of id.
Write a query to return the ID, status, and amount of every order, plus the running total of total_amount within the order's status, ordered by id.
Assumptions:
- The
orderstable has one row per order with anid, astatus, and atotal_amount. - Within each status, orders are processed in ascending
idorder. The running total at each row is the combinedtotal_amountacross every order with the samestatuswhoseidis less than or equal to that row'sid. - The running total restarts at the first order of each status.
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
The PARTITION BY status slices the table into one independent window per status, and the ORDER BY id inside each window turns the SUM into a running total within that status. Cancelled orders accumulate against cancelled orders. Delivered orders accumulate against delivered orders. The accumulator never crosses a status boundary, which is the behavior the dashboard wants.
Clause by clause
SELECT id, status, total_amountreturns each order's identifier, status, and amount unchanged. The running total per status is computed alongside.SUM(total_amount) OVER (PARTITION BY status ORDER BY id) AS status_running_totalis the window expression with two clauses insideOVER.PARTITION BY statusdivides the rows into separate groups, one per distinct status; theSUMrestarts at the first row of each group.ORDER BY idthen defines the running accumulation within each group as every row with the samestatusand a less-than-or-equalid. Cancelled order5carriesstatus_running_total = 649; cancelled order13carries649 + 79.99 = 728.99. The cancelled accumulator never sees a delivered order.FROM ordersreads every order. No filter is needed; each status receives its own running total in parallel.
Why PARTITION BY and not a separate GROUP BY per status
GROUP BY status would collapse every order in a status into a single row carrying one total, which is the wrong shape. The dashboard wants one row per order with the cumulative total attached. PARTITION BY is the windowed version of GROUP BY: it defines independent groups for the aggregation, but the rows are preserved instead of collapsed. That is exactly the difference between a windowed SUM and a grouped SUM, and it is why PARTITION BY is the right clause when the per-order detail must stay in the output.
You practiced SUM(...) OVER (PARTITION BY ... ORDER BY ...) — running total restricted to within each partition; accumulation resets at every partition boundary.
