Top Active Users With Window Functions | Dataford Interview Questions

Cengage wants to identify the most engaged users of its digital learning products each month. Write a PostgreSQL query that counts valid activity events and returns the top three users for January and February 2025.

Requirements

Count non-null activity events for each user and calendar month.
Rank users independently within each month by activity count descending, breaking ties by user_id ascending.
Return only the top three users per month, including the month, user ID, display name, activity count, and rank.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Cengage user identifier
display_name	VARCHAR(100)	User's display name
email	VARCHAR(255)	User email address

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity event identifier
user_id	INTEGER	Associated user identifier
occurred_at	TIMESTAMP	Time when the event occurred
event_type	VARCHAR(50)	Type of learning activity

Your question is Top Active Users With Window Functions. 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