Top Users by Activity | Dataford Interview Questions

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

Slack wants to identify its most active users based on messages sent during January 2025. Write a PostgreSQL query that returns the five users with the highest message activity in the specified timeframe.

Requirements

Count each user's messages from 2025-01-01 00:00:00+00 through, but not including, 2025-02-01 00:00:00+00.
Include users with no matching messages in the aggregation, even though they will not appear in the top five for this dataset.
Return the user's display name and activity count, ordered by activity descending and user_id ascending to break ties.
Return only the top five users.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Slack user identifier
display_name	VARCHAR(120)	User's Slack display name
email	VARCHAR(255)	User email address

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
user_id	INT	Slack user who sent the message
channel_name	VARCHAR(120)	Slack channel containing the message
sent_at	TIMESTAMPTZ	Timestamp when the message was sent