Rolling Three Order Sum — Window Frames in SQL

The problem

Brightlane's revenue operations team wants a rolling 3-order spend total per customer to see short-horizon spending behavior.

Write a query to return every order's ID, customer ID, order amount, and the combined total_amount across that customer's current order plus the two immediately preceding orders chronologically.

Assumptions:

Within each customer's orders, the rolling-3 sum at each row covers that order plus the two orders with the largest ordered_at strictly before it. The window is restricted to that customer.
For a customer's first order, the rolling-3 sum equals just that order's total_amount. For a customer's second order, the sum covers two orders. From the third order onward, it covers three.
The final result is sorted by customer_id ascending, then by ordered_at ascending.

Output:

One row per order, with columns id, customer_id, total_amount, and rolling_3_sum. Sorted by customer_id, then 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,
  customer_id,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at ROWS BETWEEN 2 PRECEDING
      AND CURRENT ROW
  ) AS rolling_3_sum
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is the rolling-3 trailing sum, scoped to one customer at a time. The frame slides one row at a time inside each customer's ordered history, always covering the current order plus the two physically prior rows.

Clause by clause

SELECT id, customer_id, total_amount, SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_sum returns the order's identifying columns alongside the windowed sum. PARTITION BY customer_id isolates each customer's orders into their own window; ORDER BY ordered_at sequences them chronologically; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW declares the frame as three physical positions.
FROM orders reads every order. No filter; every customer's full history is in scope.
ORDER BY customer_id, ordered_at sorts the displayed result so each customer's window reads top to bottom in time order. This outer sort is separate from the window's own ORDER BY.

The trap

The window is partial at the start of each customer's history. Customer 1's first order returns 129.98 because only one row exists yet; the second returns 378.98 summing two rows. The frame doesn't fail or skip those rows. It quietly sums whatever rows are available, which is the correct behaviour but easy to misread as a bug when comparing to a manual three-row check.

You practiced SUM(...) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) — explicit ROWS frame defining a fixed-position 3-record trailing window per partition.

Return every order's ID, customer ID, order amount, and the combined `total_amount` across that customer's current order plus the two immediately preceding orders chronologically

The shape

Clause by clause

The trap

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