#Meta/Facebook
Count the total number of distinct conversations on WhatsApp. Two users share a conversation if there is at least 1 message between them. Multiple messages between the same pair of users are considered a single conversation.
table name:whatsapp_messages
Solution:
select count(*) as cnt from
(select message_sender_id as first, message_receiver_id as second1
from whatsapp_messages
union
select message_receiver_id as first, message_sender_id as second1
from whatsapp_messages) t1
where first<second1
Output:
SQL Script:
USE [StrataScratch]
GO
CREATE TABLE [dbo].[whatsapp_messages](
[message_id] [int] NULL,
[message_date] [datetime] NULL,
[message_time] [datetime] NULL,
[message_sender_id] [varchar](50) NULL,
[message_receiver_id] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (8187, CAST(N'2021-10-09T00:00:00.000' AS DateTime), CAST(N'2023-06-07T08:34:00.000' AS DateTime), N'U1', N'U2')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5911, CAST(N'2021-10-17T00:00:00.000' AS DateTime), CAST(N'2023-06-07T11:49:00.000' AS DateTime), N'U1', N'U2')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (930, CAST(N'2021-11-09T00:00:00.000' AS DateTime), CAST(N'2023-06-07T02:20:00.000' AS DateTime), N'U1', N'U3')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5721, CAST(N'2021-11-12T00:00:00.000' AS DateTime), CAST(N'2023-06-07T02:47:00.000' AS DateTime), N'U1', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5404, CAST(N'2021-12-04T00:00:00.000' AS DateTime), CAST(N'2023-06-07T09:12:00.000' AS DateTime), N'U1', N'U3')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (6038, CAST(N'2021-10-08T00:00:00.000' AS DateTime), CAST(N'2023-06-07T02:58:00.000' AS DateTime), N'U1', N'U3')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (2738, CAST(N'2021-11-12T00:00:00.000' AS DateTime), CAST(N'2023-06-07T08:27:00.000' AS DateTime), N'U1', N'U7')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5875, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2023-06-07T12:15:00.000' AS DateTime), N'U2', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (7715, CAST(N'2021-12-25T00:00:00.000' AS DateTime), CAST(N'2023-06-07T08:27:00.000' AS DateTime), N'U2', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (9704, CAST(N'2021-12-27T00:00:00.000' AS DateTime), CAST(N'2023-06-07T00:25:00.000' AS DateTime), N'U2', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (9587, CAST(N'2021-10-21T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:57:00.000' AS DateTime), N'U2', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (3087, CAST(N'2021-10-28T00:00:00.000' AS DateTime), CAST(N'2023-06-07T02:28:00.000' AS DateTime), N'U3', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (1532, CAST(N'2021-11-17T00:00:00.000' AS DateTime), CAST(N'2023-06-07T11:38:00.000' AS DateTime), N'U3', N'U2')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (7033, CAST(N'2021-11-21T00:00:00.000' AS DateTime), CAST(N'2023-06-07T04:50:00.000' AS DateTime), N'U3', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (2186, CAST(N'2021-12-17T00:00:00.000' AS DateTime), CAST(N'2023-06-07T03:34:00.000' AS DateTime), N'U3', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (3993, CAST(N'2021-10-29T00:00:00.000' AS DateTime), CAST(N'2023-06-07T12:50:00.000' AS DateTime), N'U3', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5790, CAST(N'2021-11-27T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:33:00.000' AS DateTime), N'U3', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (3002, CAST(N'2021-12-11T00:00:00.000' AS DateTime), CAST(N'2023-06-07T11:12:00.000' AS DateTime), N'U4', N'U5')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (1347, CAST(N'2021-12-19T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:12:00.000' AS DateTime), N'U4', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (7575, CAST(N'2021-12-29T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:46:00.000' AS DateTime), N'U4', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (7886, CAST(N'2021-10-08T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:18:00.000' AS DateTime), N'U4', N'U6')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (7089, CAST(N'2021-10-19T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:54:00.000' AS DateTime), N'U5', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (4552, CAST(N'2021-12-14T00:00:00.000' AS DateTime), CAST(N'2023-06-07T04:48:00.000' AS DateTime), N'U6', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (6035, CAST(N'2021-12-21T00:00:00.000' AS DateTime), CAST(N'2023-06-07T02:28:00.000' AS DateTime), N'U6', N'U2')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5814, CAST(N'2021-12-29T00:00:00.000' AS DateTime), CAST(N'2023-06-07T06:52:00.000' AS DateTime), N'U6', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (506, CAST(N'2021-10-13T00:00:00.000' AS DateTime), CAST(N'2023-06-07T09:38:00.000' AS DateTime), N'U6', N'U2')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (3442, CAST(N'2021-10-17T00:00:00.000' AS DateTime), CAST(N'2023-06-07T05:31:00.000' AS DateTime), N'U6', N'U3')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (3457, CAST(N'2021-10-26T00:00:00.000' AS DateTime), CAST(N'2023-06-07T11:38:00.000' AS DateTime), N'U6', N'U7')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (5840, CAST(N'2021-11-01T00:00:00.000' AS DateTime), CAST(N'2023-06-07T01:20:00.000' AS DateTime), N'U6', N'U4')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (9727, CAST(N'2021-11-25T00:00:00.000' AS DateTime), CAST(N'2023-06-07T06:46:00.000' AS DateTime), N'U6', N'U4')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (9579, CAST(N'2021-10-29T00:00:00.000' AS DateTime), CAST(N'2023-06-07T11:13:00.000' AS DateTime), N'U7', N'U1')
GO
INSERT [dbo].[whatsapp_messages] ([message_id], [message_date], [message_time], [message_sender_id], [message_receiver_id]) VALUES (4759, CAST(N'2021-11-16T00:00:00.000' AS DateTime), CAST(N'2023-06-07T06:18:00.000' AS DateTime), N'U7', N'U1')
GO