Interview Drills · FIRST_VALUE · LAST_VALUE · frame trap
44. First vs latest order value
Concept: FIRST_VALUE · LAST_VALUE · frame trapshow
FIRST_VALUE(x) and LAST_VALUE(x) return x from the first and last row of the window frame. The trap: with ORDER BY, the default frame ends at the current row, so LAST_VALUE just returns the current row's own value. Widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (or use FIRST_VALUE with ORDER BY ... DESC).
Example (different data):
SELECT DISTINCT player_id,
LAST_VALUE(rating) OVER (
PARTITION BY player_id ORDER BY played_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS current_rating
FROM matches;Your task
Classic interview question: are repeat customers spending more or less over time? Compare each repeat buyer's first and latest basket.
For each customer with at least two completed orders, return customer_id, first_amount (amount of their earliest completed order), latest_amount (amount of their most recent completed order) and amount_change (latest_amount - first_amount). Sort by customer_id.
Starting Postgres in your browser…
Checking your access…