Top Active Users Per Region | Dataford Interview Questions

Your question is Top Active Users Per Region. 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

Alteryx wants to identify the most engaged users across account regions. Using activity recorded in its user event pipeline, write a PostgreSQL query that finds the top three active users in each region over the 30-day period ending on 2025-02-15.

Requirements

Join users to account details and user events.
Include only events from 2025-01-17 through 2025-02-15, inclusive. Ignore events with a NULL timestamp and accounts without a region.
Calculate each user's event count, number of active calendar days, and distinct event types.
Rank users within each region by event count descending, active days descending, and user ID ascending as the deterministic tie-breaker.
Return no more than three users per region, with the ranking included in the output.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
account_id	INT	Account associated with the user

account_details

Column	Type	Description
account_idPK	INT	Unique account identifier
region	VARCHAR(50)	Geographic region assigned to the account

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of user activity
event_timestamp	TIMESTAMP	Timestamp when the event occurred