SQL Oracle - Count of repeat call within a relative data range

Question

I am trying to mark calls by a customer as repeats, if they have made a previous call in the proceeding 5 days . If they have had more than one call in the preceding 5 days I want it to show as a count. I can do this for a static date range, but not sure how to do it for a relative one.

I have Customer ID and Call Date but wish to add the Repeat Count column.

Customer ID, Call Date, Repeat

453, 01-NOV-14, 0

453, 13-NOV-14, 0

351, 01-DEC-14, 0

453, 15-NOV-14, 1

441, 02-DEC-14, 0

102, 03-DEC-14, 0

453, 03-DEC-14, 0

441, 05-DEC-14, 1

453, 05-DEC-14, 1

453, 06-DEC-14, 2

I had seen the answer to basically the same question here, however it used the Cross Apply function which I understand is not available in Oracle; SQL keeping count of occurrences through a sliding window

Answer 1

You can do this using a correlated subquery:

select CustomerId, CallDate, 
       (select count(*)
        from table t2
        where t2.CusomerId = t.CustomerId and
              t2.CallDate >= t.CallDate - 5 and t2.CallDate < t.CallDate
       ) as Repeat
from table t;

Answer 2

/*
WITH tab AS (
SELECT 453 CustomerID, to_date('01-11-14', 'dd-mm-yy') CallDate FROM dual
UNION ALL SELECT 453, to_date('13-11-14', 'dd-mm-yy') FROM dual
union all select 351, to_date('01-12-14', 'dd-mm-yy') FROM dual
UNION ALL SELECT 453, to_date('15-11-14', 'dd-mm-yy') FROM dual
union all select 441, to_date('02-12-14', 'dd-mm-yy') FROM dual
UNION ALL SELECT 102, to_date('03-12-14', 'dd-mm-yy') FROM dual
union all select 453, to_date('03-12-14', 'dd-mm-yy') FROM dual
UNION ALL SELECT 441, to_date('05-12-14', 'dd-mm-yy') FROM dual
union all select 453, to_date('05-12-14', 'dd-mm-yy') FROM dual
UNION ALL SELECT 453, to_date('06-12-14', 'dd-mm-yy') FROM dual
)
*/
SELECT CustomerID, CallDate, 
       count(CallDate) OVER(PARTITION BY CustomerID ORDER BY CallDate RANGE BETWEEN 5 PRECEDING AND CURRENT ROW) - 1 repeat_count
FROM tab order by 2;