Top Percent Users by Activity | Dataford Interview Questions

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

Google Workspace analytics needs to identify the most active users during January 2025. Write a PostgreSQL query that calculates weighted activity, ranks every registered user, and returns the top 5%.

Requirements

Include every user, including users with no qualifying activity.
Count activity points from events joined to activity_types; events without a matching user must not contribute to any user.
Restrict events to January 1 through January 31, 2025.
Use a ranking function that preserves ties and gaps. Include every user tied at the top-5% boundary.
Return user_id, display_name, activity_score, and activity_rank, ordered by score descending and user ID ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique registered user identifier
display_name	VARCHAR(100)	User display name
created_at	DATE	Date the account was created

activity_types

Column	Type	Description
activity_type_idPK	INTEGER	Unique activity type identifier
event_name	VARCHAR(50)	Name of the tracked activity
activity_points	INTEGER	Weighted activity points

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity event identifier
user_id	INTEGER	Associated user identifier
activity_type_id	INTEGER	Associated activity type
occurred_at	TIMESTAMP	Time when the activity occurred