Top 5 Users Per Month | Dataford Interview Questions

TEKsystems wants to identify the most engaged users of its internal activity platform each month. Write a PostgreSQL query that counts valid user activity records and returns the top five users for every calendar month.

Requirements

Join user_activity to users using user_id.
Group activity by calendar month and user.
Count each user's activity records, excluding rows without an activity timestamp or matching user.
Rank users within each month by activity count descending, using user_id as a deterministic tie-breaker, and return no more than five users per month.
Return results ordered by month, rank, and user ID.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
team_name	VARCHAR(100)	TEKsystems team assignment

user_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity record identifier
user_id	INTEGER	User associated with the activity
activity_at	TIMESTAMP	Timestamp when the activity occurred
activity_type	VARCHAR(50)	Type of platform activity

Your question is Top 5 Users Per Month. 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