Attribution: Clicked But Not Purchased | Dataford Interview Questions

Asana Spa's marketing team wants to identify users who clicked an ad but did not complete a purchase within seven days of any recorded click. Write a PostgreSQL query that returns these users for campaign follow-up.

Requirements

Consider every ad click and match purchases by the same user.
Treat a purchase from the click timestamp through exactly seven days afterward as a conversion.
Return users with at least one click and no purchase in any of their seven-day click windows.
Include the user's email and earliest ad click timestamp, ordered by user_id.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Asana Spa user identifier
email	VARCHAR(255)	User email address

ad_clicks

Column	Type	Description
click_idPK	INT	Unique ad click identifier
user_id	INT	User who clicked the advertisement
campaign_name	VARCHAR(100)	Asana Spa marketing campaign name
clicked_at	TIMESTAMP	Timestamp when the ad was clicked

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who completed the purchase
purchased_at	TIMESTAMP	Timestamp when the purchase was completed
amount	DECIMAL(10,2)	Purchase amount

Your question is Attribution: Clicked But Not Purchased. Start with the requirements and the three 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