SQL for Top Activity Users | Dataford Interview Questions

Your question is SQL for Top Activity Users. Start with the requirements and the two 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

The WWT Advanced Technology Center analytics team wants to identify the most active users of its internal technology portal. Write a PostgreSQL query that returns the top 5% of users based on their number of qualifying activity events during January 2025.

Requirements

Include every user when calculating the population, including users with zero activity.
Count only events from 2025-01-01 through 2025-01-31, inclusive.
Return the smallest whole-number group representing at least 5% of users. For this dataset, that is one user.
Return user_id, user_name, activity_count, and the user's activity rank, ordered by rank ascending. Break ties by user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
email	VARCHAR(255)	User email address

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the activity event
event_type	VARCHAR(50)	Type of portal activity
occurred_at	TIMESTAMP	Activity event timestamp