Match Visits Within Five Minutes | Dataford Interview Questions

Tatari needs to measure immediate website response after each ad airing. Write a PostgreSQL query that counts website visits occurring from the airing timestamp through five minutes afterward, including both endpoints.

Requirements

Return one row for every ad airing, including airings with no qualifying visits.
Count each distinct website visit at most once per airing.
Return the airing ID, campaign, airing timestamp, and visit count, ordered chronologically by airing timestamp.

Schema

ad_airings

Column	Type	Description
airing_idPK	INTEGER	Unique identifier for the ad airing
campaign_name	VARCHAR(100)	Tatari campaign associated with the airing
aired_at	TIMESTAMP	Timestamp when the ad aired

website_visits

Column	Type	Description
visit_idPK	INTEGER	Unique identifier for the website visit
visited_at	TIMESTAMP	Timestamp when the website visit occurred
landing_page	VARCHAR(200)	Initial page visited

Your question is Match Visits Within Five Minutes. 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