Brightlane's data engineer is investigating how PostgreSQL's default ordered-window frame treats products that share the same price.
Write a query to return the ID, name, and price of every product, plus the running sum of price ordered by price ascending. Sort the final result by price ascending.
Assumptions:
- Products accumulate in ascending
priceorder. The running sum at each row uses PostgreSQL's default ordered-window frame: every product whosepriceis strictly less than the current row'sprice, plus every product tied at the current row'sprice. - When several products share the same
price, all of them receive the same running sum — that sum already includes every tied product, not just the rows up to that physical position. - The final result is sorted by
priceascending.
Output:
- One row per product, with columns
id,name,price, andrunning_price_sum. Sorted bypriceascending.
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,
name,
price,
SUM(price) OVER (
ORDER BY
price
) AS running_price_sum
FROM
products
ORDER BY
priceThe shape
The default ordered-window frame is the load-bearing detail. When ORDER BY price appears inside OVER without an explicit ROWS or RANGE clause, PostgreSQL applies its default frame: every row up to and including the current row's peer group. Two products tied at the same price are peers, and they are included in each other's running sum. So both rows tied at 12.99 carry the same running_price_sum of 25.98 — that sum already contains both of them.
Clause by clause
SELECT id, name, pricereturns each product's identifier, name, and price. The running price sum is attached.SUM(price) OVER (ORDER BY price) AS running_price_sumis the window expression.ORDER BY priceinsideOVERdefines the running accumulation, but with one twist: when no explicit frame is given, PostgreSQL groups rows by equal values on the ordering expression and includes the entire peer group in each row's frame. The first two products both priced at12.99form a peer group of size two. Each of them sees a frame containing both rows, and each of them carries the same running sum of12.99 + 12.99 = 25.98.FROM productsreads the catalog. TheORDER BY priceoutside theOVERclause is a separate ordering — it controls only the final display order of the result, not the window's internal accumulation.
Why the tied rows share a sum instead of receiving incremental values
The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under RANGE mode, "current row" means every row whose ordering value equals the current row's value, not the single physical row. The two products at 12.99 are both "current row" for each other's frame. Each frame includes both of them, and the sum is computed once over that two-row frame. The result is the same on both tied rows by design.
The trap
The natural reading of SUM(price) OVER (ORDER BY price) is a strict row-by-row accumulation. The default frame quietly does something different the moment two rows share the same value on the ordering expression. The query runs, the numbers look plausible, and the tied rows carry a sum that already includes their tied neighbors. When that behavior is not what the report needs, the fix is to add an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame, which counts by physical row position instead of by value equality.
You practiced the default RANGE frame's peer-group inclusion — SUM(...) OVER (ORDER BY x) with no explicit frame includes every row tied with the current row, so tied rows receive identical running totals.
