SQL Puzzle | The Phone Call Puzzle

10 Monday Oct 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

Tags

SQL Puzzle | The Phone Call Puzzle

Write a T-SQL query that fetches records where the person’s first and last call of the day is to the same person.

Please check out the sample input and the expected output.

Sample Inputs

ID	FromPhoneNumber	ToPhoneNumber	Starttime	EndTime
101	9878650055	9878650002	2016-10-09 12:17:48.483	2016-10-09 12:20:48.483
101	9878650055	9878650000	2016-10-09 13:17:48.483	2016-10-09 13:20:48.483
101	9878650055	9878650008	2016-10-09 14:17:48.483	2016-10-09 14:20:48.483
101	9878650055	9878650000	2016-10-09 15:17:48.483	2016-10-09 15:20:48.483
101	9878650055	9878650002	2016-10-09 20:17:48.483	2016-10-09 20:20:48.483
102	9878650051	9878650099	2016-10-09 12:17:48.483	2016-10-09 12:20:48.483
102	9878650051	9878650099	2016-10-09 12:21:48.483	2016-10-09 12:25:48.483
102	9878650051	9878650099	2016-10-09 12:29:48.483	2016-10-09 12:31:48.483
102	9878650051	9878650077	2016-10-09 20:29:48.483	2016-10-09 20:35:48.483

Expected Output

ID	FromPhoneNumber	ToPhoneNumber	Date
101	9878650055	9878650002	2016-10-09

Rules/Restrictions

The challenge is to do it with T-SQL statements only.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


CREATE TABLE PhoneLog
(
       ID INT,
       FromPhoneNumber Varchar(100),
       ToPhoneNumber Varchar(100),
       Starttime DateTime,
       EndTime DateTime
)
GO
 
INSERT INTO PhoneLog Values 

(101,9878650055,9878650002,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483'),
(101,9878650055,9878650000,'2016-10-09 13:17:48.483','2016-10-09 13:20:48.483'),
(101,9878650055,9878650008,'2016-10-09 14:17:48.483','2016-10-09 14:20:48.483'),
(101,9878650055,9878650000,'2016-10-09 15:17:48.483','2016-10-09 15:20:48.483'),
(101,9878650055,9878650002,'2016-10-09 20:17:48.483','2016-10-09 20:20:48.483'),
 
(102,9878650051,9878650099,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483'),
(102,9878650051,9878650099,'2016-10-09 12:21:48.483','2016-10-09 12:25:48.483'),
(102,9878650051,9878650099,'2016-10-09 12:29:48.483','2016-10-09 12:31:48.483'),
(102,9878650051,9878650077,'2016-10-09 20:29:48.483','2016-10-09 20:35:48.483')
 
GO



--

—

SOLUTION – 1


--

SELECT tbl.Id, tbl.FromPhoneNumber, tbl.ToPhoneNumber, tbl.distinctdate Date 
FROM 
(
       SELECT  a.Id, a.FromPhoneNumber, a.ToPhoneNumber, a.Starttime, Format(Starttime, 'MM/dd/yyyy') distinctdate
       FROM    PhoneLog a
                    OUTER APPLY ( SELECT MIN(starttime) CallDateTime
                                          FROM      Phonelog b
                                          WHERE     b.ID = a.ID AND Format(b.Starttime, 'MM/dd/yyyy') = Format(a.Starttime, 'MM/dd/yyyy')
       UNION ALL 
        SELECT MAX(starttime) 
                                          FROM      Phonelog b
                                          WHERE     b.ID = a.ID AND Format(b.Starttime, 'MM/dd/yyyy') = Format(a.Starttime, 'MM/dd/yyyy')
                                        ) C
       WHERE a.Starttime = c.CallDateTime
) tbl
GROUP BY tbl.Id, tbl.FromPhoneNumber, tbl.ToPhoneNumber, tbl.distinctdate
HAVING COUNT(tbl.ToPhoneNumber) = 2


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

13 thoughts on “SQL Puzzle | The Phone Call Puzzle”

Mitul Brahmbhatt said:

October 11, 2016 at 9:34 AM

drop table #temp
drop table #phonelog
drop table #compare
Create table #temp
(
ID INT,
FromPhoneNumber varchar(100),
Min_ToPhoneNumber varchar(100),
Max_ToPhoneNumber varchar(100),
EndTime Date,
min_num int,
max_num int
);

SELECT
ID ,
FromPhoneNumber ,
ToPhoneNumber ,
EndTime,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID ,FromPhoneNumber ,EndTime ) As Rownums,
ROW_NUMBER() OVER(PARTITION BY ID, ToPhoneNumber ORDER BY ID ,FromPhoneNumber ,EndTime ) As Nums
INTO #phonelog
FROM PhoneLog

SELECT
ID,
FromPhoneNumber,
MIN(RowNums) min_num,
MAX(RowNums) max_num
INTO #compare
FROM #phonelog
GROUP BY ID, FromPhoneNumber

Insert Into #temp (ID ,
FromPhoneNumber ,
Min_ToPhoneNumber ,
Max_ToPhoneNumber ,
EndTime ,
min_num ,
max_num)
SELECT A.ID ,
A.FromPhoneNumber ,
NULL,
A.ToPhoneNumber,
A.EndTime,
B.min_num,
B.max_num
FROM #phonelog A
JOIN #compare B ON A.ID = B.ID and (A.Rownums = B.max_num )
WHERE A.RowNums IN (SELECT MAX_NUM FROM #compare)

UPDATE #temp
Set Min_ToPhoneNumber = B.ToPhoneNumber
FROM #temp A
Join #phonelog B ON A.ID = B.ID
JOIN #compare C ON B.ID = C.ID and (B.Rownums = C.min_num)
WHERE B.RowNums IN (SELECT MIN_NUM FROM #compare)

SELECT ID INT,
FromPhoneNumber ,
Max_ToPhoneNumber ToPhoneNumber,
Convert(Date, EndTime,103) Dates
FROM #temp
WHERE 1 = (CASE WHEN Min_ToPhoneNumber = Max_ToPhoneNumber THEN 1 ELSE 0 END)
ORDER BY ID

LikeLike

Reply
- Mahesh Alam said:
  
  October 12, 2016 at 6:04 PM
  
  SELECT ID ,FromPhoneNumber,ToPhoneNumber,CONVERT(DATE ,Starttime) Date
  FROM PhoneLog
  Where Starttime In (SELECT MAX(Starttime) FROM PhoneLog GROUP BY ID)
  
  LikeLike
  
  Reply
- mitul'sqlblog said:
  
  October 14, 2016 at 10:13 AM
  
  Alternate solution: Only returns customer (unlike all) for whom first called number and last called numbers are same for the day.
  
  SELECT
  DISTINCT
  p.ID
  ,p.FromPhoneNumber
  ,max_num.ToPhoneNumber
  ,convert(date ,p.Starttime) [Date]
  FROM PhoneLog p
  JOIN (SELECT ID , ToPhoneNumber FROM PhoneLog Where Starttime In (SELECT DISTINCT MIN(Starttime) FROM PhoneLog GROUP BY ID)) min_num ON min_num.ID = p.ID
  JOIN (SELECT ID , ToPhoneNumber FROM PhoneLog Where Starttime In (SELECT DISTINCT MAX(Starttime) FROM PhoneLog GROUP BY ID)) max_num ON max_num.ID = p.ID
  WHERE 1 = (CASE WHEN min_num.ToPhoneNumber = max_num.ToPhoneNumber THEN 1 ELSE 0 END)
  
  LikeLike
  
  Reply
Mahesh Alam said:

October 12, 2016 at 5:38 PM

SELECT ID ,FromPhoneNumber,ToPhoneNumber,CONVERT(DATE ,Starttime) Date
FROM PhoneLog
Where Starttime In (SELECT MAX(Starttime) FROM PhoneLog GROUP BY ID)

LikeLike

Reply
Mahesh Alam said:

October 12, 2016 at 5:40 PM

Used the below
SELECT ID ,FromPhoneNumber,ToPhoneNumber,CONVERT(DATE ,Starttime) Date
FROM PhoneLog
Where Starttime In (SELECT MAX(Starttime) FROM PhoneLog GROUP BY ID)

LikeLike

Reply
Mata Leon (@MAWLtd) said:

October 23, 2016 at 3:17 AM

with firstCall as (
select
*
from
PhoneLog p
where
p.Starttime = (select min(f.Starttime) from PhoneLog f where p.ID = f.ID)
),
lastCall as (
select
*
from
PhoneLog p
where
p.Starttime = (select max(l.Starttime) from PhoneLog l where p.ID = l.ID)
)
select
f.ID
, f.FromPhoneNumber
, f.ToPhoneNumber
, f.Starttime
, convert(date, f.EndTime) as “Date”
from
firstCall f
inner join lastCall l
on f.ToPhoneNumber = l.ToPhoneNumber

LikeLike

Reply
Manol Manolov said:

October 30, 2016 at 10:03 AM

SELECT
a.ID,
a.FromPhoneNumber,
a.ToPhoneNumber,
a.Date
FROM (
SELECT DISTINCT
ID,
FromPhoneNumber,
Date = CAST(Starttime AS DATE),
ToPhoneNumber = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime) ,
ToPhoneNumberLast = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime DESC)
FROM PhoneLog) a
WHERE ToPhoneNumber = ToPhoneNumberLast

LikeLike

Reply
Manol Manolov said:

October 30, 2016 at 10:06 AM

SELECT
a.ID,
a.FromPhoneNumber,
a.ToPhoneNumber,
a.Date
FROM (
SELECT DISTINCT
ID,
FromPhoneNumber,
Date = CAST(Starttime AS DATE),
ToPhoneNumber = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime) ,
ToPhoneNumberLast = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime DESC)
FROM #PhoneLog) a
WHERE ToPhoneNumber = ToPhoneNumberLast

LikeLike

Reply
Manol Manolov said:

October 30, 2016 at 10:08 AM

— on SQL 2012 and up use:
SELECT
a.ID,
a.FromPhoneNumber,
a.ToPhoneNumber,
a.Date
FROM (
SELECT DISTINCT
ID,
FromPhoneNumber,
Date = CAST(Starttime AS DATE),
ToPhoneNumber = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime) ,
ToPhoneNumberLast = FIRST_VALUE(ToPhoneNumber) OVER (PARTITION BY CAST(Starttime AS DATE),FromPhoneNumber ORDER BY Starttime DESC)
FROM #PhoneLog) a
WHERE ToPhoneNumber = ToPhoneNumberLast

LikeLike

Reply
Pawan Kumar Khowal said:

November 21, 2016 at 12:57 PM

Good One!!

LikeLike

Reply
Pawan Kumar Khowal said:

November 21, 2016 at 12:58 PM

Great !!

LikeLike

Reply
Subhash Sharma said:

December 4, 2016 at 6:42 PM

select id,FromPhoneNumber,ToPhoneNumber, count(*) as calls from (
select a.* from PhoneLog a join
(select id, FromPhoneNumber, min(starttime) as starttime from PhoneLog
group by id,FromPhoneNumber) b on
a.id=b.id and
a.FromPhoneNumber=b.FromPhoneNumber and
a.Starttime=b.starttime
union all
select a.* from PhoneLog a join
(select id, FromPhoneNumber, max(starttime) as starttime from PhoneLog
group by id,FromPhoneNumber) b on
a.id=b.id and
a.FromPhoneNumber=b.FromPhoneNumber and
a.Starttime=b.starttime ) a group by id,FromPhoneNumber,ToPhoneNumber
having count(*) > 1

LikeLike

Reply
revank219 said:

July 6, 2021 at 1:38 PM

with cte as (
select *,ROW_NUMBER()over(Partition by ID order by Starttime)rnk from PhoneLog
)
select a.ID,A.FromPhoneNumber,a.ToPhoneNumber,a.Starttime from cte a inner join cte b
on a.id = b.id
where a.ToPhoneNumber = b.ToPhoneNumber
and (a.rnk = (select min(rnk) from cte c) and b.rnk = (select max(rnk) from cte d))

LikeLike

Reply