Rolling 7-Day Click-Through Rate | Dataford Interview Questions

Cisco monitors ad engagement across its digital advertising surfaces. Given ad event data, write a PostgreSQL query that calculates each user's rolling 7-day click-through rate by event date.

Requirements

Aggregate daily clicks and impressions per user.
Treat a click-through rate as clicks divided by impressions, returning NULL when the rolling impression count is zero.
Build a complete calendar for each user so the 7-day window represents calendar days, including days without events.
Use a window function to calculate rolling clicks, impressions, and CTR over the current date plus the six preceding calendar dates.
Return only dates on which the user had at least one event, ordered by user_id and event_date.

Schema

ad_events

Column	Type	Description
event_idPK	INT	Unique ad event identifier
user_id	INT	Cisco user identifier
event_ts	TIMESTAMPTZ	Timestamp at which the ad event occurred
event_type	VARCHAR(20)	Event classification, such as click or impression

Your question is Rolling 7-Day Click-Through Rate. Start with the requirements and the one table 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

Cisco monitors ad engagement across its digital advertising surfaces. Given ad event data, write a PostgreSQL query that calculates each user's rolling 7-day click-through rate by event date.

Requirements

Aggregate daily clicks and impressions per user.
Treat a click-through rate as clicks divided by impressions, returning NULL when the rolling impression count is zero.
Build a complete calendar for each user so the 7-day window represents calendar days, including days without events.
Use a window function to calculate rolling clicks, impressions, and CTR over the current date plus the six preceding calendar dates.
Return only dates on which the user had at least one event, ordered by user_id and event_date.

Schema

ad_events

Column	Type	Description
event_idPK	INT	Unique ad event identifier
user_id	INT	Cisco user identifier
event_ts	TIMESTAMPTZ	Timestamp at which the ad event occurred
event_type	VARCHAR(20)	Event classification, such as click or impression

Your question is Rolling 7-Day Click-Through Rate. Start with the requirements and the one table 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.