Brightlane's logistics team reviews the complete order stream as a single chronological sequence — orders from every customer interleaved in time order. Each order should appear alongside the amount of the order placed immediately before it across the entire stream.
Write a query to return every order's ID, order amount, and the preceding order amount in the global chronological sequence, sorted by ordered_at.
Assumptions:
- The preceding order is the order with the largest
ordered_atstrictly before the current row'sordered_at, across every customer. - For the very first order in the stream — where no preceding order is on record — the preceding-amount value is missing.
- The final result is sorted by
ordered_atascending.
Output:
- One row per order, with columns
id,total_amount, andprev_order_amount. Sorted byordered_at.
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,
total_amount,
LAG(total_amount) OVER (
ORDER BY
ordered_at
) AS prev_order_amount
FROM
orders
ORDER BY
ordered_atThe shape
Dropping PARTITION BY collapses every order into a single global window, so LAG reaches back one position across the entire chronological stream regardless of which customer placed each order. The "previous" order is whoever placed an order immediately before, not the same customer's previous purchase.
Clause by clause
SELECT id, total_amount, LAG(total_amount) OVER (ORDER BY ordered_at) AS prev_order_amountreturns each order's ID, amount, and the dollar amount of the order placed immediately before it in time. The window has noPARTITION BY, which means the entire orders table is one window;ORDER BY ordered_atsorts that single window chronologically;LAGlooks back one row in that single sequence.FROM ordersreads every order.ORDER BY ordered_atsorts the printed result chronologically.
Why drop PARTITION BY here and not keep it
The logistics team's question is about the full order stream as one interleaved sequence. Adding PARTITION BY customer_id would change the question to "what did this customer order previously," which is a different report. The absence of partitioning is load-bearing; it makes the lookup span across customers, not within them.
The trap
The very first order in the entire table (the earliest ordered_at across all customers) has no prior row anywhere in the data, so LAG returns NULL on that one row. Every other row has a predecessor, because the window covers every order. This is one NULL for the whole result, not one NULL per customer.
You practiced LAG(column) OVER (ORDER BY ...) without a PARTITION BY — the entire result is one window; the offset reaches across every record in the stream.
