Running Totals in Python

A running total grows row by row: after each order, the revenue so far. SUM(total) on its own collapses all the rows into one. As a window function, SUM(total) OVER (ORDER BY ordered) keeps every row and adds up the totals from the first row to the current one.

Put PARTITION BY before the ORDER BY and the sum starts again in each group. This query counts each customer's orders so far, with COUNT(*) instead of SUM:

query = (
    "SELECT customer_id, ordered, "
        "COUNT(*) OVER (PARTITION BY customer_id ORDER BY ordered) AS order_no "
    "FROM orders "
    "ORDER BY customer_id, ordered "
)

The ORDER BY inside OVER (…) only says in which order to add up. To print the rows in a set order, the query needs its own ORDER BY at the end.

The CTE order_totals is given: one row per order that wasn't cancelled, with its total. Your query must print:

ordered | customer_id | total | customer_so_far | revenue_so_far
2026-03-02 | 1 | 34 | 34 | 34
2026-03-15 | 2 | 29 | 29 | 63
2026-04-05 | 1 | 41 | 75 | 104
2026-04-18 | 3 | 27 | 27 | 131
2026-05-07 | 4 | 73 | 73 | 204
2026-05-30 | 3 | 62 | 89 | 266
(6 rows)

Your task: below the CTE, select ordered, customer_id and total from order_totals, plus two running totals of total: customer_so_far, which starts again for each customer, and revenue_so_far for the whole shop. Sort the rows by ordered.

Assignment
Follow the Coding Tutorial to write the main query below the CTE: the three columns, revenue_so_far, FROM order_totals, then customer_so_far above revenue_so_far, and the final ORDER BY.

Hint
In the window, PARTITION BY customer_id comes before ORDER BY ordered. The customer_so_far line ends with a comma, because revenue_so_far comes after it.