Attribution With 24-Hour Window | Dataford Interview Questions

Given a table of ad impressions and a table of user conversions, write a query to attribute conversions to the most recent ad impression within a 24-hour window.

Use inclusive 24-hour boundaries and resolve equal-time impression ties using the highest impression_id. Exclude conversions without a qualifying impression.

Output

One row per attributed conversion with conversion_id, user_id, conversion_time, impression_id, and impression_time.
Order by conversion_time, then conversion_id.

Schema

ad_impressions

Column	Type	Description
impression_idPK	INT	Unique identifier for the ad impression
user_id	INT	Identifier of the user exposed to the ad
impression_time	TIMESTAMP	Timestamp when the ad impression occurred

user_conversions

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

Your question is Attribution With 24-Hour Window. 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