Company: Slack | Difficulty: easy
Slack's workspace analytics team wants to review activity in active channels during February 2025.
Return the channel_id, channel_name, and number of messages posted in each active channel during February 2025 as message_count. Include active channels with no messages during that month with a message count of zero. Exclude archived channels.
channel_id
channel_name
message_count
Sort by message_count descending, then channel_id ascending.
channels
is_archived
messages
message_id
posted_at
message_text
The #engineering channel has two messages during February, while #marketing and #announcements have none. The archived #old-project channel is excluded even though it has a February message.
#engineering
#marketing
#announcements
#old-project
DROP TABLE IF EXISTS messages; DROP TABLE IF EXISTS channels; CREATE TABLE channels ( channel_id INT, channel_name VARCHAR(100), is_archived BOOLEAN ); CREATE TABLE messages ( message_id INT, channel_id INT, posted_at TIMESTAMP, message_text VARCHAR(500) ); INSERT INTO channels VALUES (1, '#engineering', FALSE), (2, '#marketing', FALSE), (3, '#old-project', TRUE), (4, '#announcements', FALSE); INSERT INTO messages VALUES (101, 1, '2025-02-03 09:00:00', 'Deploy completed'), (102, 1, '2025-02-10 10:00:00', 'Reviewing the release'), (103, 2, '2025-01-31 16:00:00', 'January update'), (104, 3, '2025-02-05 11:00:00', 'Archived project note');
← All SQL Challenges · Slack Questions · Learn SQL Free