Users Exposed to Multiple Campaigns | Dataford Interview Questions

Your question is Users Exposed to Multiple Campaigns. 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

Netflix marketing analytics needs to identify members who saw multiple active campaigns during the same calendar week. Write a PostgreSQL query that deduplicates repeated exposures to the same campaign before counting campaigns.

Requirements

Consider weeks beginning on Monday using PostgreSQL DATE_TRUNC('week', exposed_at).
Include only exposures with a non-null timestamp and campaigns whose status is active.
Count each distinct campaign once per user and week, even if the user had repeated exposure events.
Return the user ID, email, week start date, number of distinct campaigns, and a comma-separated alphabetical list of campaign names.
Return only user-week combinations with at least two distinct campaigns, ordered by user ID and week.

Schema

users

Column	Type	Description
user_idPK	INT	Netflix member identifier
email	VARCHAR(255)	Member email address

campaign_exposures

Column	Type	Description
exposure_idPK	INT	Exposure event identifier
user_id	INT	Referenced member
campaign_id	INT	Referenced campaign
exposed_at	TIMESTAMP	Timestamp when the campaign was shown
channel	VARCHAR(50)	Netflix marketing surface or channel

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
campaign_name	VARCHAR(150)	Campaign name
status	VARCHAR(20)	Campaign lifecycle status