Top Chatters Message Frequency | Dataford Interview Questions

Twitch wants to identify the most active chatters in each channel during the first week of August 2026. Write a PostgreSQL query using Twitch channel and chat event data to rank registered chatters by message frequency.

Requirements

Include only message events from August 1 through August 7, 2026, inclusive.
Return the top two registered chatters per Twitch channel, ranked by message count descending.
Include each chatter's message count and percentage of all qualifying messages in that channel.
Break ties deterministically by username and user ID, and order the final results by channel name and rank.

Schema

channels

Column	Type	Description
channel_idPK	INT	Twitch channel identifier
channel_name	VARCHAR(100)	Channel display name

users

Column	Type	Description
user_idPK	INT	Registered Twitch user identifier
username	VARCHAR(50)	Twitch username

chat_events

Column	Type	Description
event_idPK	INT	Chat event identifier
channel_id	INT	Referenced Twitch channel
user_id	INT	User who generated the event
event_type	VARCHAR(30)	Event category
event_time	TIMESTAMP	Time at which the event occurred

Your question is Top Chatters Message Frequency. Start with the requirements and the three 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