Join and Aggregate User Behavior | Dataford Interview Questions

Your question is Join and Aggregate User Behavior. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Dentsu International's Merkury audience activation data includes users, campaigns, and behavioral events. Write a PostgreSQL query that summarizes campaign performance during January 2025.

Requirements

Use campaigns as the reporting base so campaigns with no in-window activity remain in the output.
Count distinct engaged users and calculate impressions, clicks, conversions, and total conversion value.
Calculate click-through rate as clicks divided by impressions and conversion rate as conversions divided by clicks. Return NULL when the denominator is zero.
Order results by campaign_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique audience user identifier
user_name	VARCHAR(100)	User display name
segment	VARCHAR(50)	Audience segment assigned to the user

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(150)	Campaign name
channel	VARCHAR(50)	Marketing activation channel

user_events

Column	Type	Description
event_idPK	INT	Unique behavioral event identifier
user_id	INT	User associated with the event
campaign_id	INT	Campaign that generated the event
event_time	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(30)	Behavioral event type
event_value	DECIMAL(12,2)	Attributed monetary value, generally populated for conversions