Sum of Current and Next Two — Window Frames in SQL

The problem

Brightlane's logistics team needs a forward-looking view of the global order pipeline — every order paired with the combined revenue of its two immediate successors in chronological order.

Write a query to return every order's ID, ordered-at timestamp, total amount, and the combined total_amount across that order plus the two orders placed immediately after it in the global chronological sequence.

Assumptions:

The next-3 sum at each row covers that order plus the two orders with the smallest ordered_at strictly after it across the entire stream.
For the last order in the stream, the sum equals just that order's total_amount. For the second-to-last, it covers two orders. From the third-to-last back through the start, it covers three.
The final result is sorted by ordered_at ascending.

Output:

One row per order, with columns id, ordered_at, total_amount, and next_3_sum. Sorted by ordered_at.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  ordered_at,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY
      ordered_at ROWS BETWEEN CURRENT ROW
      AND 2 FOLLOWING
  ) AS next_3_sum
FROM
  orders
ORDER BY
  ordered_at

The shape

SUM(total_amount) OVER (ORDER BY ordered_at ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) is a forward-looking frame. Instead of summing the current row plus rows behind it, it sums the current row plus the next two rows in the global chronological sequence.

Clause by clause

SELECT id, ordered_at, total_amount, SUM(total_amount) OVER (ORDER BY ordered_at ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS next_3_sum returns the order's identifying columns and the forward-looking sum. There's no PARTITION BY, so the window is the entire orders table; ORDER BY ordered_at sequences every order chronologically across the whole stream; ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING declares the frame as the current row plus the next two physical rows.
FROM orders reads every order.
ORDER BY ordered_at sorts the result for display.

The trap

The window shrinks at the end of the stream, not the start. The third-to-last order sees three rows; the second-to-last sees two; the last sees one (itself). That mirror image of the usual partial-window behaviour catches people who assume the partial frame always appears at the top of the result. With FOLLOWING frames, the partial rows are at the bottom.

You practiced ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING — a forward-looking frame that covers the current record plus its next two successors in order.

Return every order's ID, ordered-at timestamp, total amount, and the combined `total_amount` across that order plus the two orders placed immediately after it in the global chronological sequence

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.