Join my list to get latest updates on articles and courses.

Hello All -

I hope you all are enjoying the holiday season. I wanted to personally thank you for all your reviews, comments and messages. It’s really been an overwhelming experience. Students occasionally ask me for some problem sets, that are challenging to solve and problems that might be helpful during interviews and white boarding questions or improve TSQL Skills. Here is an interesting problem that I came across.

You may refer the details of the problem set below and leave your answers in the comments section in my blog. I will be sure to have a look and provide my 2 cents. Please note, that I won't be providing readymade answers, but will comment on your solutions.
I will add more interesting problems as I come across and be sure to send a note to all. I hope this will be helpful for your SQL interviews or screening calls.

Happy holidays!

Quiz # 1:

Imagine that a call center office works from 9 am to 5 pm from Monday to Friday and has 5 employees. The office is trying to analyze what % of work that is done in that 9am – 5pm timeframe and what percentage is vacant.

Consider the below example:

Row 1 logs time worked by employee # 1. He takes a phone call from 9 am – 9:30 am, then 10 am to 3pm.

Row 2 logs time worked by employee #2. He takes a phone call from 9:15 am to 9:50 am then 4pm to 5 pm.


If we look at this data:

Total time available for the office was from 9 am to 5 pm = 8 hours

In those 8 hours, total time when any work was done by any employee was 9am to 9:50am (check the overlap) + 10am to 3pm + 4pm to 5 pm = 50 mins + 5hours + 1 hr. = 6 hour and 50 mins

In short, you need to sum up the time by every employee and count only once whenever there is an overlap.

The logs are stored as below in a SQL table:

Employee Name

Call Start Time

Call End Time

Employee # 1

9 am

9:30 am

Employee # 2

9:15 am

9:50 am

Employee # 3

9:20 am

9:30 am

Employee # 1

10 am

3 pm

Employee # 4

10:30 am

11 am

Employee # 2

4 pm

5 pm

Employee # 4

4:15 pm

4:430 pm

You need to write a query that outputs the following:

Total Time available = 8 hours

Total time Utilized = 6 hour 50 mins.

Script for creating the table:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CallCenterLogs](

[EmployeeName] [varchar](50) NOT NULL,

[CallStartTime] [datetime] NOT NULL,

[CallEndTime] [datetime] NOT NULL

) ON [PRIMARY]

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee1', CAST(N'2017-12-08T09:00:00.000' AS DateTime), CAST(N'2017-12-08T09:30:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee2', CAST(N'2017-12-08T09:15:00.000' AS DateTime), CAST(N'2017-12-08T09:50:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee3', CAST(N'2017-12-08T09:20:00.000' AS DateTime), CAST(N'2017-12-08T09:30:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee1', CAST(N'2017-12-08T10:00:00.000' AS DateTime), CAST(N'2017-12-08T15:00:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee4', CAST(N'2017-12-08T10:30:00.000' AS DateTime), CAST(N'2017-12-08T11:00:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee2', CAST(N'2017-12-08T16:00:00.000' AS DateTime), CAST(N'2017-12-08T17:00:00.000' AS DateTime))

GO

INSERT [dbo].[CallCenterLogs] ([EmployeeName], [CallStartTime], [CallEndTime]) VALUES (N'Employee4', CAST(N'2017-12-08T16:15:00.000' AS DateTime), CAST(N'2017-12-08T16:30:00.000' AS DateTime))

GO

/****** Script for SelectTopNRows command from SSMS ******/

SELECT TOP (1000) [EmployeeName]

,[CallStartTime]

,[CallEndTime]

FROM [TestDB].[dbo].[CallCenterLogs]

Good luck and please put your scripts, answers and questions in the comments section.


Solutions Submitted by people:

1 - https://pastebin.com/CFYd6g1E (by Mark Lund).