Last-Click Attribution Query | Dataford Interview Questions

Given a table of user clicks and a table of conversions, write a query to attribute conversions to the last clicked ad before the purchase event at Affirm.

Use only clicks strictly before each purchase. If multiple eligible clicks have the same timestamp, choose the greatest click_id. Include conversions with no eligible click.

Output

One row per conversion, ordered by purchase_at, then conversion_id
Columns: conversion_id, user_id, purchase_at, conversion_amount, attributed_ad_id, attributed_click_id, and click_at
Use NULL attribution fields when no eligible click exists

Schema

user_clicks

Column	Type	Description
click_idPK	INT	Unique click identifier
user_id	INT	User who clicked the ad
ad_id	VARCHAR(50)	Identifier of the clicked ad
clicked_at	TIMESTAMP	Timestamp when the ad was clicked

conversions

Column	Type	Description
conversion_idPK	INT	Unique purchase conversion identifier
user_id	INT	User who completed the purchase
purchase_at	TIMESTAMP	Timestamp when the purchase occurred
conversion_amount	DECIMAL(10,2)	Purchase amount

Your question is Last-Click Attribution Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem