First and Last Order per Customer

Amazon's retention analytics team wants to see each buyer's first and last purchased item alongside their full order history — useful for spotting category migration (e.g., the buyer who started in Books and is now deep in Electronics). For each customer's orders, show the customer_id, order_id, product, order_date, first_product (the product from their earliest order), and last_product (the product from their latest order). Use FIRST_VALUE and LAST_VALUE window functions. Sort by customer_id, order_date. FIRST_VALUE/LAST_VALUE are an Amazon interview staple because they force you to reason about frame clauses — the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW silently breaks LAST_VALUE, and most candidates ship wrong results without noticing.