SQL Windows: Partitions and Running Totals

Use a window function when you need each original row plus a calculation across related rows. Before accepting an AI-generated window query, check the partition, the ordering and the frame. Those choices determine which rows contribute to each answer. This exercise produces regional running totals and a row number without collapsing individual sales.

Start with five sales

Download the SQL fixture and queries. Run sqlite3 :memory: < sql-windows.sql in a local terminal, or use a fresh PostgreSQL practice database. The example uses whole-number amounts so the expected results can be checked by hand.

North totals 35 and South totals 15. A grouped query would return one row per region. The window query below keeps all five rows and adds the cumulative amount for that region.

Make the frame explicit

SELECT id, region, amount,
       ROW_NUMBER() OVER (
         PARTITION BY region ORDER BY day, id
       ) AS regional_row,
       SUM(amount) OVER (
         PARTITION BY region ORDER BY day, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales
ORDER BY region, day, id;

PARTITION BY region restarts the calculation for each region. ORDER BY day, id supplies a deterministic order when two sales share a date. The explicit ROWS frame includes rows from the start of that partition through the current row. PostgreSQL documents how windows retain rows and how frames change the rows included in a calculation.

Predict every output row

The regional row number is an ordering label, not a stable business identifier. Adding an earlier sale can change later row numbers. The sale ID remains the identifier supplied by the fixture. Explain this distinction before using row numbers to join results elsewhere.

Explore two plausible mistakes

Remove PARTITION BY region while retaining the date-and-ID order. The running total now mixes regions and finishes at 50. That is a valid overall total but answers a different question. A query being syntactically correct does not tell you whether its grouping matches the reader's need.

Next, order only by day and omit the explicit frame. With the usual default frame, same-date peers are included together in the cumulative sum. North's first two rows can both show 30. The download includes this comparison. If you want one cumulative step per sale, keep a deterministic tie-breaker and the explicit row frame.

Verify independently of the generated code

Check that the output contains five rows, each sale ID appears once, and the final running total of each partition equals that region's grouped sum. Add a North sale of 4 on September 3; North should finish at 39 while South remains 15.

Ask an assistant to explain the first North row that differs between the two frame choices. Then reconstruct the query without copying it. If joins already inflated the input, window functions will preserve that problem: complete the join double-counting exercise first.

Sources and further practice

PostgreSQL: Window Functions supports the reference principle used here. The exercise, example data and review routine on this page are original Paradigm Study teaching examples.

For a broader workflow, see coding learners. Bring your attempt and the step that confused you into Paradigm Study for a lesson or focused practice. Start a learning notebook.