Initial Call Duration

Redfin helps clients to find agents. Each client will have a unique request_id and each request_id has several calls. For each request_id, the first call is an “initial call” and all the following calls are “update calls”. What's the average call duration for all initial calls?

table name: redfin_call_tracking


Solution:

select 1.0*avg(call_duration) as duration
from redfin_call_tracking
where id in(
select min(id) as first_call
from redfin_call_tracking
group by request_id)

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[redfin_call_tracking](
	[created_on] [datetime] NULL,
	[request_id] [int] NULL,
	[call_duration] [int] NULL,
	[id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T04:08:04.000' AS DateTime), 2, 3, 1)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T05:28:47.000' AS DateTime), 1, 28, 2)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T07:27:36.000' AS DateTime), 2, 22, 3)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T13:18:21.000' AS DateTime), 1, 12, 4)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T15:08:08.000' AS DateTime), 2, 13, 5)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T16:27:23.000' AS DateTime), 1, 19, 6)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T17:38:01.000' AS DateTime), 3, 15, 7)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-01T17:56:39.000' AS DateTime), 2, 25, 8)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T00:10:10.000' AS DateTime), 1, 28, 9)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T00:45:49.000' AS DateTime), 3, 19, 10)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T04:28:44.000' AS DateTime), 3, 28, 11)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T04:52:52.000' AS DateTime), 1, 13, 12)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T05:02:41.000' AS DateTime), 3, 24, 13)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T06:19:56.000' AS DateTime), 3, 5, 14)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T07:25:59.000' AS DateTime), 2, 20, 15)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T08:20:25.000' AS DateTime), 1, 21, 16)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T10:45:35.000' AS DateTime), 3, 23, 17)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T13:38:59.000' AS DateTime), 1, 11, 18)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T15:02:29.000' AS DateTime), 2, 19, 19)
GO
INSERT [dbo].[redfin_call_tracking] ([created_on], [request_id], [call_duration], [id]) VALUES (CAST(N'2020-03-02T17:13:13.000' AS DateTime), 1, 2, 20)
GO


Comments (0)
Questions List