StrataScratch
Meta/Facebook Messenger stores the number of messages between users in a table named 'fb_messages'. In this table 'user1' is the sender, 'user2' is the receiver, and 'msg_count' is the number of messages exchanged between them.
Find the top 10 most active users on Meta/Facebook Messenger by counting their total number of messages sent and received. Your solution should output usernames and the count of the total messages they sent or received
table name: fb_messages
Solution:
with cte as
(
select user1 as _user,sum(msg_count) as msg_count from fb_messages
group by user1
union
select user2 as _user,sum(msg_count) as msg_count from fb_messages
group by user2
)
select top 10 _user,msg_count from cte order by msg_count desc
Output:
SQL Script:
USE [StrataScratch]
CREATE TABLE [dbo].[fb_messages](
[id] [int] NOT NULL,
[date] [datetime] NULL,
[user1] [varchar](50) NULL,
[user2] [varchar](50) NULL,
[msg_count] [int] NULL,
CONSTRAINT [PK_fb_messages] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (1, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'kpena', N'scottmartin', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (2, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'misty19', N'srogers', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (3, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'jerome75', N'craig23', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (4, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'taylorhoward', N'johnmccann', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (5, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'wangdenise', N'sgoodman', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (6, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'wolfelizabeth', N'sgoodman', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (7, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'robbinsbrenda', N'morenowayne', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (8, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'trobinson', N'lindsey38', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (9, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'kirk72', N'tanya26', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (10, CAST(N'2020-08-02T00:00:00.000' AS DateTime), N'davidfoster', N'lindsey38', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (11, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'rtaylor', N'kristenharris', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (12, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'wangdenise', N'mdavis', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (13, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'jennifer11', N'johnmccann', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (14, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'timothyjohnson', N'lisa58', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (15, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'taylorhoward', N'lfisher', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (16, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'perezanita', N'brandi30', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (17, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'herringcarlos', N'lindsey38', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (18, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'iwilcox', N'murrayheidi', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (19, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'brandi30', N'perezanita', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (20, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'jennifer11', N'craig23', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (21, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'brandi30', N'lindsey38', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (22, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'uwilliamson', N'lfisher', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (23, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'ucrawford', N'sgoodman', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (24, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'wangdenise', N'murrayheidi', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (25, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'iwilcox', N'reneebailey', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (26, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'wolfelizabeth', N'srogers', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (27, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'ucrawford', N'johnmccann', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (28, CAST(N'2020-08-03T00:00:00.000' AS DateTime), N'ferrellkeith', N'craig23', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (29, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'kpena', N'johnmccann', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (30, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'ujefferson', N'mendozamisty', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (31, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'herringcarlos', N'navarrokimberly', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (32, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'misty19', N'mdavis', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (33, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'trobinson', N'hrodriguez', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (34, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'aliciacohen', N'scottmartin', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (35, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'kpena', N'sgoodman', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (36, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'herringcarlos', N'lindsey38', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (37, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'herringcarlos', N'johnmccann', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (38, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'ucrawford', N'jake28', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (39, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'achang', N'jessicaburns', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (40, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'stewartdavid', N'millersteven', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (41, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'ucrawford', N'lfisher', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (42, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'iwilcox', N'tanya26', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (43, CAST(N'2020-08-04T00:00:00.000' AS DateTime), N'hughesscott', N'murrayheidi', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (44, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'hughesscott', N'johnmccann', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (45, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'jerome75', N'jessicaburns', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (46, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'wangdenise', N'johnmccann', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (47, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'jennifer11', N'mendozamisty', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (48, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'jennifer11', N'tanya26', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (49, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'ujefferson', N'lewiscarrie', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (50, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'ferrellkeith', N'scottmartin', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (51, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'misty19', N'scottmartin', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (52, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'achang', N'hrodriguez', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (53, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'wolfelizabeth', N'jessicaburns', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (54, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'ferrellkeith', N'jake28', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (55, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'misty19', N'molinalaura', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (56, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'timothyjohnson', N'campbellsteven', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (57, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'rtaylor', N'scottmartin', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (58, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'trobinson', N'molinalaura', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (59, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'charles52', N'tanya26', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (60, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'timothy58', N'srogers', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (61, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'herringcarlos', N'jake28', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (62, CAST(N'2020-08-05T00:00:00.000' AS DateTime), N'hughesscott', N'randalljoseph', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (63, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'shane83', N'johnmccann', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (64, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'stewartdavid', N'lisa58', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (65, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'kirk72', N'perezanita', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (66, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'wangdenise', N'johnmccann', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (67, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'trobinson', N'scottmartin', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (68, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'ucrawford', N'campbellsteven', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (69, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'achang', N'lewiscarrie', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (70, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'kirk72', N'stephenmiller', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (71, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'kirk72', N'lisa58', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (72, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'shane83', N'sgoodman', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (73, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'misty19', N'tanya26', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (74, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'jerome75', N'craig23', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (75, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'ferrellkeith', N'tanya26', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (76, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'lopezsamantha', N'tanya26', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (77, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'julian53', N'craig23', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (78, CAST(N'2020-08-06T00:00:00.000' AS DateTime), N'robbinsbrenda', N'lfisher', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (79, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'hughesscott', N'craig23', 7)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (80, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'kpena', N'chrisdrake', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (81, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'taylorhoward', N'lfisher', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (82, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'herringcarlos', N'srogers', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (83, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'timothy58', N'srogers', 3)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (84, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'achang', N'millersteven', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (85, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'ucrawford', N'scottmartin', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (86, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'ferrellkeith', N'lewiscarrie', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (87, CAST(N'2020-08-07T00:00:00.000' AS DateTime), N'robbinsbrenda', N'lfisher', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (88, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'julian53', N'mdavis', 1)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (89, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'stewartdavid', N'perezanita', 9)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (90, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'kpena', N'campbellsteven', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (91, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'trobinson', N'tanya26', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (92, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'jerome75', N'lfisher', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (93, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'wangdenise', N'randalljoseph', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (94, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'wangdenise', N'craig23', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (95, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'timothyjohnson', N'mdavis', 5)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (96, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'charles52', N'mdavis', 6)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (97, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'trobinson', N'tanya26', 2)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (98, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'jennifer11', N'terry96', 4)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (99, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'achang', N'craig23', 8)
GO
INSERT [dbo].[fb_messages] ([id], [date], [user1], [user2], [msg_count]) VALUES (100, CAST(N'2020-08-08T00:00:00.000' AS DateTime), N'jerome75', N'campbellsteven', 4)
GO