SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…