Objective

Write a SQL query to identify the top two users who sent the most messages in August 2022 on Slack.

Table Schema:

• messages

Task Requirements:

• Filter only messages sent in August 2022
• Count the total messages sent by each user
• Identify the top two users with the highest message counts
• Return user IDs with their message counts sorted in descending order
  
  

Output columns: user_id, message_count

SELECT 
    user_id,
    COUNT(message_id) AS message_count
FROM messages
WHERE EXTRACT(YEAR FROM sent_date) = 2022
    AND EXTRACT(MONTH FROM sent_date) = 8
GROUP BY user_id
ORDER BY message_count DESC
LIMIT 2;

Explanation

Step 1: Filter Messages from August 2022

Use EXTRACT to filter rows where the year is 2022 and the month is 8 (August).

WHERE EXTRACT(YEAR FROM sent_date) = 2022
    AND EXTRACT(MONTH FROM sent_date) = 8

Step 2: Group by User and Count Messages

Group by user_id and count messages per user.

COUNT(message_id) AS message_count

Step 3: Sort by Message Count and Limit to Top 2

Order by message_count descending and apply LIMIT 2 to return only the two most active users.

ORDER BY message_count DESC
LIMIT 2