Drill 12 of 12 in Window Functions
0

Window Functions · FIRST_VALUE · LAST_VALUE · frame trap

12. 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…