Top Users by Region | Dataford Interview Questions

Acumen wants to identify its most engaged users in each region. As of February 15, 2025, write a PostgreSQL query to find the top three users per region based on engagement points earned during the previous 30 days.

Requirements

Include engagement events from January 16 through February 15, 2025, inclusive.
Sum each user's non-null score within the period.
Rank users independently within each region using ROW_NUMBER(), ordering by total score descending and user_id ascending as the tie-breaker.
Return only ranks 1 through 3, with regions ordered alphabetically and users ordered by rank.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Acumen user identifier
user_name	VARCHAR(100)	User display name
region	VARCHAR(50)	User geographic region

engagement_events

Column	Type	Description
event_idPK	INTEGER	Unique engagement event identifier
user_id	INTEGER	References users.user_id
event_at	TIMESTAMP	Timestamp when the engagement occurred
event_type	VARCHAR(50)	Type of engagement action
score	NUMERIC(10,2)	Points assigned to the event

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