By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table interactions( id int, dateinteracted datetime);
insert into interactions values
(1401,'2017-05-29 09:16:00')
,(1401,'2017-06-28 09:11:00')
,(1501,'2017-06-02 09:16:00')
,(1366,'2017-07-03 18:26:00')
,(1366,'2017-07-03 18:22:00')
,(1366,'2017-07-28 20:32:00')
,(1401,'2017-07-28 09:11:00')
,(1212,'2017-08-24 13:05:00')
,(1651,'2017-09-15 15:07:00')
,(1366,'2017-09-18 09:15:00')
,(1366,'2017-10-02 09:16:00')
,(1401,'2017-10-02 09:16:00')
;
with t as(
select *
,datediff(d,lag(dateinteracted,1)over(partition by id order by dateinteracted)
,dateinteracted) dif1
,datediff(d,lag(dateinteracted,2)over(partition by id order by dateinteracted)
,dateinteracted) dif2
,datediff(d,lag(dateinteracted,3)over(partition by id order by dateinteracted)
,dateinteracted) dif3
,row_number()over(order by dateinteracted) rn
from interactions
)
select *
from t t1
left join t t2
on t1.id=t2.id
and t2.dateinteracted<t1.dateinteracted
and t2.dateinteracted>(t1.dateinteracted-90)
order by t1.dateinteracted;
| id | dateinteracted | dif1 | dif2 | dif3 | rn | id | dateinteracted | dif1 | dif2 | dif3 | rn |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1401 | 2017-05-29 09:16:00.000 | null | null | null | 1 | null | null | null | null | null | null |
| 1501 | 2017-06-02 09:16:00.000 | null | null | null | 2 | null | null | null | null | null | null |
| 1401 | 2017-06-28 09:11:00.000 | 30 | null | null | 3 | 1401 | 2017-05-29 09:16:00.000 | null | null | null | 1 |
| 1366 | 2017-07-03 18:22:00.000 | null | null | null | 4 | null | null | null | null | null | null |
| 1366 | 2017-07-03 18:26:00.000 | 0 | null | null | 5 | 1366 | 2017-07-03 18:22:00.000 | null | null | null | 4 |
| 1401 | 2017-07-28 09:11:00.000 | 30 | 60 | null | 6 | 1401 | 2017-05-29 09:16:00.000 | null | null | null | 1 |
| 1401 | 2017-07-28 09:11:00.000 | 30 | 60 | null | 6 | 1401 | 2017-06-28 09:11:00.000 | 30 | null | null | 3 |
| 1366 | 2017-07-28 20:32:00.000 | 25 | 25 | null | 7 | 1366 | 2017-07-03 18:22:00.000 | null | null | null | 4 |
| 1366 | 2017-07-28 20:32:00.000 | 25 | 25 | null | 7 | 1366 | 2017-07-03 18:26:00.000 | 0 | null | null | 5 |
| 1212 | 2017-08-24 13:05:00.000 | null | null | null | 8 | null | null | null | null | null | null |
| 1651 | 2017-09-15 15:07:00.000 | null | null | null | 9 | null | null | null | null | null | null |
| 1366 | 2017-09-18 09:15:00.000 | 52 | 77 | 77 | 10 | 1366 | 2017-07-03 18:22:00.000 | null | null | null | 4 |
| 1366 | 2017-09-18 09:15:00.000 | 52 | 77 | 77 | 10 | 1366 | 2017-07-03 18:26:00.000 | 0 | null | null | 5 |
| 1366 | 2017-09-18 09:15:00.000 | 52 | 77 | 77 | 10 | 1366 | 2017-07-28 20:32:00.000 | 25 | 25 | null | 7 |
| 1366 | 2017-10-02 09:16:00.000 | 14 | 66 | 91 | 11 | 1366 | 2017-07-28 20:32:00.000 | 25 | 25 | null | 7 |
| 1366 | 2017-10-02 09:16:00.000 | 14 | 66 | 91 | 11 | 1366 | 2017-09-18 09:15:00.000 | 52 | 77 | 77 | 10 |
| 1401 | 2017-10-02 09:16:00.000 | 66 | 96 | 126 | 12 | 1401 | 2017-07-28 09:11:00.000 | 30 | 60 | null | 6 |
with gr as(
select t1.id,t1.dateinteracted
,count(*) cnt
from interactions t1
left join interactions t2
on t1.id=t2.id
and t2.dateinteracted<t1.dateinteracted
and t2.dateinteracted>(t1.dateinteracted-90)
group by t1.id,t1.dateinteracted
)
,grday as(
select cast(dateinteracted as date) cdate
,sum(case when cnt>=2 then 1 else 0 end) cnt_in_day
from gr
where cnt>=2
group by cast(dateinteracted as date)
)
select * from gr --grday
order by id,dateinteracted
| id | dateinteracted | cnt |
|---|---|---|
| 1212 | 2017-08-24 13:05:00.000 | 1 |
| 1366 | 2017-07-03 18:22:00.000 | 1 |
| 1366 | 2017-07-03 18:26:00.000 | 1 |
| 1366 | 2017-07-28 20:32:00.000 | 2 |
| 1366 | 2017-09-18 09:15:00.000 | 3 |
| 1366 | 2017-10-02 09:16:00.000 | 2 |
| 1401 | 2017-05-29 09:16:00.000 | 1 |
| 1401 | 2017-06-28 09:11:00.000 | 1 |
| 1401 | 2017-07-28 09:11:00.000 | 2 |
| 1401 | 2017-10-02 09:16:00.000 | 1 |
| 1501 | 2017-06-02 09:16:00.000 | 1 |
| 1651 | 2017-09-15 15:07:00.000 | 1 |