For each order, show the amount of the customer's PREVIOUS order in chronological order. The first order per customer gets NULL.
Sort by customer_id then order_ts then order_id ascending.
Returncustomer_idorder_idamount_usdprev_amount
About this problem
This problem practises the most basic use of LAG: looking one row back inside a defined sequence. The business question is simple – for every order in orders, how much did the same customer spend last time? You need to define two things before the function means anything: whose history counts as “previous” (the customer) and what “previous” means (chronological by order_ts, with order_id to settle any tie). Every order appears in the result, whatever its status, and each customer’s first order shows prev_amount as NULL because nothing came before it.
The common mistake is writing LAG(amount_usd) OVER (ORDER BY order_ts) with no PARTITION BY customer_id. The query runs and every row gets a plausible-looking number, but the sequence is the whole table in time order, not each customer’s own history. In the sample, c_001 and c_002 place orders on interleaved days in May, so a global window hands c_001’s second order the amount of c_002’s first order. Only one row in the whole result would show NULL, which is a quick tell that the window is wrong. Ordering by order_id is another slip: o_012 was backfilled with a later order_id but an earlier order_ts than c_004’s other 22 May orders, so it lands in the wrong place. Filtering to paid orders is a third, since the prompt asks for each order. Replacing the first order’s NULL with 0 is a quieter one: “no previous order” and “a previous order worth nothing” are different facts.
Read the lesson: LAG, LEAD & Value Functions (P17) →