Tuesday, 22 October 2013

Identifying close friends from call records

The data sleuths at telcos do this all the time: identifying close friends from call records. Then use that information to target people with advertisements.

Consider a call records table  that has the caller number, receiver number and call start time. Find those cases where the caller calls the same number first and last.

create table phone_records (
src  numeric,
dest numeric,
call_st_time datetime
)


insert into phone_records (src,dest,call_st_time) values (1,100,'2013-01-01 08:00:00')
insert into phone_records (src,dest,call_st_time) values (1,200,'2013-01-01 08:10:00')
insert into phone_records (src,dest,call_st_time) values (1,300,'2013-01-01 08:20:00')
insert into phone_records (src,dest,call_st_time) values (1,100,'2013-01-01 08:30:00')
insert into phone_records (src,dest,call_st_time) values (2,100,'2013-01-01 08:40:00')
insert into phone_records (src,dest,call_st_time) values (3,100,'2013-01-01 08:50:00')
insert into phone_records (src,dest,call_st_time) values (3,100,'2013-01-01 08:51:00')
insert into phone_records (src,dest,call_st_time) values (3,100,'2013-01-01 08:52:00')
insert into phone_records (src,dest,call_st_time) values (4,100,'2013-01-01 08:53:00')
insert into phone_records (src,dest,call_st_time) values (4,200,'2013-01-01 08:54:00')

Here caller 1 has called the same number first and last. So have 2 and 3. But 4 has not.
Here's the query that can extract this information in one shot:

select src, (case when first_num = last_num then 'Y' else 'N' end) is_match from
(select src, max(case when rn_asc = 1 then dest else -1 end) first_num, max(case when rn_desc = 1 then dest else -1 end) last_num
from
(select * from (
select src, dest, call_st_time,
rank() over (partition by src order by call_st_time) rn_asc, rank() over (partition by src order by call_st_time desc) rn_desc
from phone_records) v1
where rn_asc = 1 or rn_desc = 1) v2 group by src)  v3

No comments:

Post a Comment