Back to questions
Teams Power Users [Microsoft SQL Interview Question]
Write a query to identify the top 2 Power Users who sent the highest number of messages on Microsoft Teams in August 2022. Display the IDs of these 2 users along with the total number of messages they sent. Output the results in descending order based on the count of the messages.
Assumption:
- No two users have sent the same number of messages in August 2022.
Table:
| Column Name | Type |
|---|---|
| message_id | integer |
| sender_id | integer |
| receiver_id | integer |
| content | varchar |
| sent_date | datetime |
Example Input:
| message_id | sender_id | receiver_id | content | sent_date |
|---|---|---|---|---|
| 901 | 3601 | 4500 | You up? | 08/03/2022 00:00:00 |
| 902 | 4500 | 3601 | Only if you're buying | 08/03/2022 00:00:00 |
| 743 | 3601 | 8752 | Let's take this offline | 06/14/2022 00:00:00 |
| 922 | 3601 | 4500 | Get on the call | 08/10/2022 00:00:00 |
Example Output:
| sender_id | message_count |
|---|---|
| 3601 | 2 |
| 4500 | 1 |
The dataset you are querying against may have different input & output - this is just an example!
Solution
To find the top 2 Power Users who sent the most messages on Microsoft Teams in August 2022, we need to first determine the count of messages sent by each user, which we'll refer to as "senders".
We start by extracting the month and year from the field and filtering the results to only include messages sent in August 2022. We then use the GROUP BY clause to group the messages by and calculate the count of messages using the function:
You can learn more about the function here, and about the GROUP BY clause here.
Here's the output from the query:
| sender_id | count_messages |
|---|---|
| 2520 | 3 |
| 3601 | 4 |
| 4500 | 1 |
The output of this query will provide the count of messages for each sender as shown in the example table.
Since we assume that no two users have sent the same number of messages in August 2022, we can simply use an ORDER BY clause in descending order to sort the results based on the count of messages.
Finally, we use a LIMIT clause to restrict the results to only the top 2 senders, giving us the desired outcome.
PostgreSQL 14