Acceptance Rate By Date

#Meta/Facebook

What is the overall friend acceptance rate by date? Your output should have the rate of acceptances by the date the request was sent. Order by the earliest date to latest.

Assume that each friend request starts by a user sending (i.e., user_id_sender) a friend request to another user (i.e., user_id_receiver) that's logged in the table with action = 'sent'. If the request is accepted, the table logs action = 'accepted'. If the request is not accepted, no record of action = 'accepted' is logged.

table name: fb_friend_requests


Solution:

with cte as
(
select user_id_sender,user_id_receiver,min(date) as date,
sum(case when action='sent' then 1 else 0 end) as sent,
sum(case when action='accepted' then 1 else 0 end) as accepted
from fb_friend_requests 
group by user_id_sender,user_id_receiver
)
select date, convert(float, 1.0*sum(accepted)/sum(sent)) as percentage_acceptance from cte group by date

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[fb_friend_requests](
	[user_id_sender] [varchar](50) NULL,
	[user_id_receiver] [varchar](50) NULL,
	[date] [datetime] NULL,
	[action] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'ad4943sdz', N'948ksx123d', CAST(N'2020-01-04T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'ad4943sdz', N'948ksx123d', CAST(N'2020-01-06T00:00:00.000' AS DateTime), N'accepted')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'dfdfxf9483', N'9djjjd9283', CAST(N'2020-01-04T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'dfdfxf9483', N'9djjjd9283', CAST(N'2020-01-15T00:00:00.000' AS DateTime), N'accepted')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'ffdfff4234234', N'lpjzjdi4949', CAST(N'2020-01-06T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'fffkfld9499', N'993lsldidif', CAST(N'2020-01-06T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'fffkfld9499', N'993lsldidif', CAST(N'2020-01-10T00:00:00.000' AS DateTime), N'accepted')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'fg503kdsdd', N'ofp049dkd', CAST(N'2020-01-04T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'fg503kdsdd', N'ofp049dkd', CAST(N'2020-01-10T00:00:00.000' AS DateTime), N'accepted')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'hh643dfert', N'847jfkf203', CAST(N'2020-01-04T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'r4gfgf2344', N'234ddr4545', CAST(N'2020-01-06T00:00:00.000' AS DateTime), N'sent')
GO
INSERT [dbo].[fb_friend_requests] ([user_id_sender], [user_id_receiver], [date], [action]) VALUES (N'r4gfgf2344', N'234ddr4545', CAST(N'2020-01-11T00:00:00.000' AS DateTime), N'accepted')
GO


Comments (0)
Questions List