Channel Activity
ASKED IN INTERVIEWSlack
Problem Statement
Slack administrators want to identify the most active channels in the workspace to better understand user engagement. Write an SQL query to rank channels by the total number of messages sent in the last 30 days.
Rules:
- Consider only messages sent in the last 30 days (Current Date: 2026-02-11).
- Rank channels by message count in descending order.
- In case of a tie in message count, return the channels in alphabetical order.
- Output columns: channel_name, message_count, activity_rank.
- Sort the final result by activity_rank in ascending order, then channel_name in ascending order.
Table Schema:
- Channels: channel_id, channel_name, created_at.
- Messages: message_id, channel_id, sender_id, message_text, sent_at.
Tests your understanding of
Window Functions, Join, DateTime and Ranking
Input Tables
Channels
| channel_id(INTEGER) | channel_name(VARCHAR) |
|---|---|
| 1 | general |
| 2 | engineering |
| 3 | marketing |
Messages
| message_id(INTEGER) | channel_id(INTEGER) | sent_at(TIMESTAMP) |
|---|---|---|
| 101 | 1 | 2026-02-10 10:00:00 |
| 102 | 1 | 2026-02-10 10:05:00 |
| 103 | 2 | 2026-02-09 15:00:00 |
Expected Output
| channel_name(VARCHAR) | message_count(INTEGER) | activity_rank(INTEGER) |
|---|---|---|
| general | 2 | 1 |
| engineering | 1 | 2 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsJoinDateTimeRanking
15-20 min
52%