Top 10 Users by Activity | Dataford Interview Questions

Netsmart Technologies wants to identify the most active users of myAvatar during January 2025. Write a PostgreSQL query that ranks users by recorded activity.

Requirements

Include every user, including users with no qualifying activity.
Count each user's activities from January 1 through January 31, 2025, and calculate the number of distinct active days.
Return the top 10 users, ordered by activity count descending, active days descending, and user_id ascending as a deterministic tie-breaker.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
full_name	VARCHAR(100)	User's display name
email	VARCHAR(255)	User's email address
role_name	VARCHAR(50)	myAvatar access role

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	Associated user identifier
activity_type	VARCHAR(50)	Type of myAvatar activity
occurred_at	TIMESTAMP	Timestamp when the activity occurred

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