Last-Click Attribution SQL | Dataford Interview Questions

Given a table of user clicks and a table of conversions, write a query to attribute conversions to the last-clicked marketing channel.

Use clicks occurring at or before each conversion, selecting the latest click per conversion. Return every conversion, including conversions without an eligible click.

Output

One row per conversion with conversion_id, user_id, click_id, marketing_channel, and converted_at
Use NULL for click fields when no eligible click exists
Order by converted_at ascending, then conversion_id ascending

Schema

user_clicks

Column	Type	Description
click_idPK	BIGINT	Unique identifier for the click
user_id	BIGINT	Identifier of the user who clicked
clicked_at	TIMESTAMP	Timestamp when the click occurred
marketing_channel	VARCHAR(50)	Marketing channel associated with the click

conversions

Column	Type	Description
conversion_idPK	BIGINT	Unique identifier for the conversion
user_id	BIGINT	Identifier of the converting user
converted_at	TIMESTAMP	Timestamp when the conversion occurred

Your question is Last-Click Attribution SQL. 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