Write a query to find the top 2 power users who
sent the most 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 count of the messages.
Assumption:
No two users has sent the same number of messages
in August 2022.
table name: messages
Solution:
select top 2 sender_id, count(message_id) as message_count from messages
where month(sent_date) = 8 AND year(sent_date) = 2022 group by sender_id
order by message_count desc
Output:
SQL Script:
CREATE TABLE [dbo].[messages](
[message_id]
[int] NULL,
[sender_id]
[int] NULL,
[receiver_id]
[int] NULL,
[content]
[varchar](550) NULL,
[sent_date]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (100, 2520, 6987, N'Send this out now!', CAST(N'2022-08-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (922, 3601, 4500, N'Get on the call', CAST(N'2022-08-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (819, 2310, 4500, N'What''s the status on this?', CAST(N'2022-07-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (743, 3601, 8752, N'Let''s take this offline', CAST(N'2022-06-14T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (902, 4500, 3601, N'Only if you''re buying', CAST(N'2022-08-08T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (901, 3601, 4500, N'You up?', CAST(N'2022-08-08T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (966, 3601, 7852, N'Meet me in five!', CAST(N'2022-08-17T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (942, 2520, 3561, N'How much do you know about Data
Science?', CAST(N'2022-08-17T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (888, 3601, 7855, N'DataLemur has awesome user base!', CAST(N'2022-12-08T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (898, 2520, 9630, N'Are you ready for your upcoming
presentation?', CAST(N'2022-08-13T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[messages] ([message_id], [sender_id], [receiver_id], [content], [sent_date]) VALUES (990, 2520, 8520, N'Maybe it was done by the automation
process.', CAST(N'2022-08-13T00:00:00.000' AS DateTime))
GO