Last Touch Before Purchase — Solution & ExplanationProblems / Last Touch Before Purchase / EditorialApproach

Candidate generation. Join purchases to same-user touches inside [purchase_time - 7 days, purchase_time]. One purchase may collect several candidate touches; a repeat buyer's purchases each collect their own.

Pick the latest per purchase. ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY touch_time DESC), keep rn = 1 — arg-max by recency at the purchase grain.

Re-attach the untouched purchases. LEFT JOIN winners back to all purchases and COALESCE(channel, 'organic'). (The PySpark variant folds steps 1–3 together by LEFT JOINing candidates directly — an unmatched purchase carries one NULL row, ranks first in its own partition, and coalesces to organic.)

Count per channel, ordered by count with an alphabetical tie-break.

Key InsightThis is the as-of join — "for each left row, the most recent right row not after it" — the time-series workhorse behind price snapshots, config-at-event-time, and last-touch attribution. Relational engines express it as *bounded join + rank + keep-first*, and the grain discipline is everything: rank per purchase, or one purchase's touch silently answers for another. The 'organic' default is the other half of real attribution — absence of a match is itself a category, which is why the LEFT JOIN can't be skipped.Hints

The partition key for the ranking is purchase_id, not user_id — a repeat buyer gets an independent backward lookup for every purchase.

Time-window the join on both ends: touch_time <= purchase_time AND touch_time >= purchase_time - 7 days, both inclusive. The data plants a touch at exactly 7 days before (counts) and one at 8 days (doesn't).

Purchases with zero qualifying touches must survive to be labeled 'organic' — that means a LEFT JOIN somewhere: either LEFT JOIN the touches then rank, or inner-join candidates and LEFT JOIN the winners back to all purchases.

ROW_NUMBER ... ORDER BY touch_time DESC then rn = 1 is 'the latest qualifying row' — the DESC twin of the arg-min idiom.

Solve Last Touch Before Purchase yourself →