Top K Users From Chat Logs (SQL) | Dataford Interview Questions

Given chat logs, find the top k users with the most frequent chat activity.

Asked in the Virtual Onsite stage. A variant of Top K Frequent Elements (LeetCode 347). Discussed merge sort, max heap, and quick select approaches, including pseudocode, time complexity, and space complexity analysis.

Task

Write a SQL query to return the top k users by message count from a chat log table.

Requirements

Count chat messages per user.
Rank users by message count descending, then by user_id ascending for ties.
Return only the top k users.

Output

Schema

chat_logs

Column	Type	Description
log_idPK	BIGINT	Unique chat log row identifier
user_id	BIGINT	User who sent the chat message
message_id	BIGINT	Unique message identifier
sent_at	TIMESTAMP	Timestamp when the message was sent

Your question is Top K Users From Chat Logs (SQL). Start with the requirements and the one table 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