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')
,(1401,'2018-02-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 |
| 1401 | 2018-02-02 09:16:00.000 | 123 | 189 | 219 | 13 | null | null | null | null | null | null |
with dates as(
select 1 lvl,cast(min(cast(dateinteracted as date)) as datetime) as dt
,cast(max(cast(dateinteracted as date)) as datetime) maxdt from interactions
union all
select lvl+1,dt+1,maxdt from dates where dt+1<=maxdt --and lvl<10
)
,act_in_day as(
select *
from dates d
left join interactions i
on cast(d.dt as date)>=cast(i.dateinteracted as date)
and cast(d.dt as date)<cast((i.dateinteracted+90) as date)
)
,id_act_in_day as(
select id,dt
,count(*) cnt
from act_in_day
group by id,dt
)
,act_by_day as(
select dt
,sum(case when cnt>=3 then 1 else 0 end) cnt_in_day
from id_act_in_day
--where cnt>=2
group by dt
)
select * from act_by_day
order by dt
option (maxrecursion 1000);
| dt | cnt_in_day |
|---|---|
| 2017-05-29 00:00:00.000 | 0 |
| 2017-05-30 00:00:00.000 | 0 |
| 2017-05-31 00:00:00.000 | 0 |
| 2017-06-01 00:00:00.000 | 0 |
| 2017-06-02 00:00:00.000 | 0 |
| 2017-06-03 00:00:00.000 | 0 |
| 2017-06-04 00:00:00.000 | 0 |
| 2017-06-05 00:00:00.000 | 0 |
| 2017-06-06 00:00:00.000 | 0 |
| 2017-06-07 00:00:00.000 | 0 |
| 2017-06-08 00:00:00.000 | 0 |
| 2017-06-09 00:00:00.000 | 0 |
| 2017-06-10 00:00:00.000 | 0 |
| 2017-06-11 00:00:00.000 | 0 |
| 2017-06-12 00:00:00.000 | 0 |
| 2017-06-13 00:00:00.000 | 0 |
| 2017-06-14 00:00:00.000 | 0 |
| 2017-06-15 00:00:00.000 | 0 |
| 2017-06-16 00:00:00.000 | 0 |
| 2017-06-17 00:00:00.000 | 0 |
| 2017-06-18 00:00:00.000 | 0 |
| 2017-06-19 00:00:00.000 | 0 |
| 2017-06-20 00:00:00.000 | 0 |
| 2017-06-21 00:00:00.000 | 0 |
| 2017-06-22 00:00:00.000 | 0 |
| 2017-06-23 00:00:00.000 | 0 |
| 2017-06-24 00:00:00.000 | 0 |
| 2017-06-25 00:00:00.000 | 0 |
| 2017-06-26 00:00:00.000 | 0 |
| 2017-06-27 00:00:00.000 | 0 |
| 2017-06-28 00:00:00.000 | 0 |
| 2017-06-29 00:00:00.000 | 0 |
| 2017-06-30 00:00:00.000 | 0 |
| 2017-07-01 00:00:00.000 | 0 |
| 2017-07-02 00:00:00.000 | 0 |
| 2017-07-03 00:00:00.000 | 0 |
| 2017-07-04 00:00:00.000 | 0 |
| 2017-07-05 00:00:00.000 | 0 |
| 2017-07-06 00:00:00.000 | 0 |
| 2017-07-07 00:00:00.000 | 0 |
| 2017-07-08 00:00:00.000 | 0 |
| 2017-07-09 00:00:00.000 | 0 |
| 2017-07-10 00:00:00.000 | 0 |
| 2017-07-11 00:00:00.000 | 0 |
| 2017-07-12 00:00:00.000 | 0 |
| 2017-07-13 00:00:00.000 | 0 |
| 2017-07-14 00:00:00.000 | 0 |
| 2017-07-15 00:00:00.000 | 0 |
| 2017-07-16 00:00:00.000 | 0 |
| 2017-07-17 00:00:00.000 | 0 |
| 2017-07-18 00:00:00.000 | 0 |
| 2017-07-19 00:00:00.000 | 0 |
| 2017-07-20 00:00:00.000 | 0 |
| 2017-07-21 00:00:00.000 | 0 |
| 2017-07-22 00:00:00.000 | 0 |
| 2017-07-23 00:00:00.000 | 0 |
| 2017-07-24 00:00:00.000 | 0 |
| 2017-07-25 00:00:00.000 | 0 |
| 2017-07-26 00:00:00.000 | 0 |
| 2017-07-27 00:00:00.000 | 0 |
| 2017-07-28 00:00:00.000 | 2 |
| 2017-07-29 00:00:00.000 | 2 |
| 2017-07-30 00:00:00.000 | 2 |
| 2017-07-31 00:00:00.000 | 2 |
| 2017-08-01 00:00:00.000 | 2 |
| 2017-08-02 00:00:00.000 | 2 |
| 2017-08-03 00:00:00.000 | 2 |
| 2017-08-04 00:00:00.000 | 2 |
| 2017-08-05 00:00:00.000 | 2 |
| 2017-08-06 00:00:00.000 | 2 |
| 2017-08-07 00:00:00.000 | 2 |
| 2017-08-08 00:00:00.000 | 2 |
| 2017-08-09 00:00:00.000 | 2 |
| 2017-08-10 00:00:00.000 | 2 |
| 2017-08-11 00:00:00.000 | 2 |
| 2017-08-12 00:00:00.000 | 2 |
| 2017-08-13 00:00:00.000 | 2 |
| 2017-08-14 00:00:00.000 | 2 |
| 2017-08-15 00:00:00.000 | 2 |
| 2017-08-16 00:00:00.000 | 2 |
| 2017-08-17 00:00:00.000 | 2 |
| 2017-08-18 00:00:00.000 | 2 |
| 2017-08-19 00:00:00.000 | 2 |
| 2017-08-20 00:00:00.000 | 2 |
| 2017-08-21 00:00:00.000 | 2 |
| 2017-08-22 00:00:00.000 | 2 |
| 2017-08-23 00:00:00.000 | 2 |
| 2017-08-24 00:00:00.000 | 2 |
| 2017-08-25 00:00:00.000 | 2 |
| 2017-08-26 00:00:00.000 | 2 |
| 2017-08-27 00:00:00.000 | 1 |
| 2017-08-28 00:00:00.000 | 1 |
| 2017-08-29 00:00:00.000 | 1 |
| 2017-08-30 00:00:00.000 | 1 |
| 2017-08-31 00:00:00.000 | 1 |
| 2017-09-01 00:00:00.000 | 1 |
| 2017-09-02 00:00:00.000 | 1 |
| 2017-09-03 00:00:00.000 | 1 |
| 2017-09-04 00:00:00.000 | 1 |
| 2017-09-05 00:00:00.000 | 1 |
| 2017-09-06 00:00:00.000 | 1 |
| 2017-09-07 00:00:00.000 | 1 |
| 2017-09-08 00:00:00.000 | 1 |
| 2017-09-09 00:00:00.000 | 1 |
| 2017-09-10 00:00:00.000 | 1 |
| 2017-09-11 00:00:00.000 | 1 |
| 2017-09-12 00:00:00.000 | 1 |
| 2017-09-13 00:00:00.000 | 1 |
| 2017-09-14 00:00:00.000 | 1 |
| 2017-09-15 00:00:00.000 | 1 |
| 2017-09-16 00:00:00.000 | 1 |
| 2017-09-17 00:00:00.000 | 1 |
| 2017-09-18 00:00:00.000 | 1 |
| 2017-09-19 00:00:00.000 | 1 |
| 2017-09-20 00:00:00.000 | 1 |
| 2017-09-21 00:00:00.000 | 1 |
| 2017-09-22 00:00:00.000 | 1 |
| 2017-09-23 00:00:00.000 | 1 |
| 2017-09-24 00:00:00.000 | 1 |
| 2017-09-25 00:00:00.000 | 1 |
| 2017-09-26 00:00:00.000 | 1 |
| 2017-09-27 00:00:00.000 | 1 |
| 2017-09-28 00:00:00.000 | 1 |
| 2017-09-29 00:00:00.000 | 1 |
| 2017-09-30 00:00:00.000 | 1 |
| 2017-10-01 00:00:00.000 | 0 |
| 2017-10-02 00:00:00.000 | 1 |
| 2017-10-03 00:00:00.000 | 1 |
| 2017-10-04 00:00:00.000 | 1 |
| 2017-10-05 00:00:00.000 | 1 |
| 2017-10-06 00:00:00.000 | 1 |
| 2017-10-07 00:00:00.000 | 1 |
| 2017-10-08 00:00:00.000 | 1 |
| 2017-10-09 00:00:00.000 | 1 |
| 2017-10-10 00:00:00.000 | 1 |
| 2017-10-11 00:00:00.000 | 1 |
| 2017-10-12 00:00:00.000 | 1 |
| 2017-10-13 00:00:00.000 | 1 |
| 2017-10-14 00:00:00.000 | 1 |
| 2017-10-15 00:00:00.000 | 1 |
| 2017-10-16 00:00:00.000 | 1 |
| 2017-10-17 00:00:00.000 | 1 |
| 2017-10-18 00:00:00.000 | 1 |
| 2017-10-19 00:00:00.000 | 1 |
| 2017-10-20 00:00:00.000 | 1 |
| 2017-10-21 00:00:00.000 | 1 |
| 2017-10-22 00:00:00.000 | 1 |
| 2017-10-23 00:00:00.000 | 1 |
| 2017-10-24 00:00:00.000 | 1 |
| 2017-10-25 00:00:00.000 | 1 |
| 2017-10-26 00:00:00.000 | 0 |
| 2017-10-27 00:00:00.000 | 0 |
| 2017-10-28 00:00:00.000 | 0 |
| 2017-10-29 00:00:00.000 | 0 |
| 2017-10-30 00:00:00.000 | 0 |
| 2017-10-31 00:00:00.000 | 0 |
| 2017-11-01 00:00:00.000 | 0 |
| 2017-11-02 00:00:00.000 | 0 |
| 2017-11-03 00:00:00.000 | 0 |
| 2017-11-04 00:00:00.000 | 0 |
| 2017-11-05 00:00:00.000 | 0 |
| 2017-11-06 00:00:00.000 | 0 |
| 2017-11-07 00:00:00.000 | 0 |
| 2017-11-08 00:00:00.000 | 0 |
| 2017-11-09 00:00:00.000 | 0 |
| 2017-11-10 00:00:00.000 | 0 |
| 2017-11-11 00:00:00.000 | 0 |
| 2017-11-12 00:00:00.000 | 0 |
| 2017-11-13 00:00:00.000 | 0 |
| 2017-11-14 00:00:00.000 | 0 |
| 2017-11-15 00:00:00.000 | 0 |
| 2017-11-16 00:00:00.000 | 0 |
| 2017-11-17 00:00:00.000 | 0 |
| 2017-11-18 00:00:00.000 | 0 |
| 2017-11-19 00:00:00.000 | 0 |
| 2017-11-20 00:00:00.000 | 0 |
| 2017-11-21 00:00:00.000 | 0 |
| 2017-11-22 00:00:00.000 | 0 |
| 2017-11-23 00:00:00.000 | 0 |
| 2017-11-24 00:00:00.000 | 0 |
| 2017-11-25 00:00:00.000 | 0 |
| 2017-11-26 00:00:00.000 | 0 |
| 2017-11-27 00:00:00.000 | 0 |
| 2017-11-28 00:00:00.000 | 0 |
| 2017-11-29 00:00:00.000 | 0 |
| 2017-11-30 00:00:00.000 | 0 |
| 2017-12-01 00:00:00.000 | 0 |
| 2017-12-02 00:00:00.000 | 0 |
| 2017-12-03 00:00:00.000 | 0 |
| 2017-12-04 00:00:00.000 | 0 |
| 2017-12-05 00:00:00.000 | 0 |
| 2017-12-06 00:00:00.000 | 0 |
| 2017-12-07 00:00:00.000 | 0 |
| 2017-12-08 00:00:00.000 | 0 |
| 2017-12-09 00:00:00.000 | 0 |
| 2017-12-10 00:00:00.000 | 0 |
| 2017-12-11 00:00:00.000 | 0 |
| 2017-12-12 00:00:00.000 | 0 |
| 2017-12-13 00:00:00.000 | 0 |
| 2017-12-14 00:00:00.000 | 0 |
| 2017-12-15 00:00:00.000 | 0 |
| 2017-12-16 00:00:00.000 | 0 |
| 2017-12-17 00:00:00.000 | 0 |
| 2017-12-18 00:00:00.000 | 0 |
| 2017-12-19 00:00:00.000 | 0 |
| 2017-12-20 00:00:00.000 | 0 |
| 2017-12-21 00:00:00.000 | 0 |
| 2017-12-22 00:00:00.000 | 0 |
| 2017-12-23 00:00:00.000 | 0 |
| 2017-12-24 00:00:00.000 | 0 |
| 2017-12-25 00:00:00.000 | 0 |
| 2017-12-26 00:00:00.000 | 0 |
| 2017-12-27 00:00:00.000 | 0 |
| 2017-12-28 00:00:00.000 | 0 |
| 2017-12-29 00:00:00.000 | 0 |
| 2017-12-30 00:00:00.000 | 0 |
| 2017-12-31 00:00:00.000 | 0 |
| 2018-01-01 00:00:00.000 | 0 |
| 2018-01-02 00:00:00.000 | 0 |
| 2018-01-03 00:00:00.000 | 0 |
| 2018-01-04 00:00:00.000 | 0 |
| 2018-01-05 00:00:00.000 | 0 |
| 2018-01-06 00:00:00.000 | 0 |
| 2018-01-07 00:00:00.000 | 0 |
| 2018-01-08 00:00:00.000 | 0 |
| 2018-01-09 00:00:00.000 | 0 |
| 2018-01-10 00:00:00.000 | 0 |
| 2018-01-11 00:00:00.000 | 0 |
| 2018-01-12 00:00:00.000 | 0 |
| 2018-01-13 00:00:00.000 | 0 |
| 2018-01-14 00:00:00.000 | 0 |
| 2018-01-15 00:00:00.000 | 0 |
| 2018-01-16 00:00:00.000 | 0 |
| 2018-01-17 00:00:00.000 | 0 |
| 2018-01-18 00:00:00.000 | 0 |
| 2018-01-19 00:00:00.000 | 0 |
| 2018-01-20 00:00:00.000 | 0 |
| 2018-01-21 00:00:00.000 | 0 |
| 2018-01-22 00:00:00.000 | 0 |
| 2018-01-23 00:00:00.000 | 0 |
| 2018-01-24 00:00:00.000 | 0 |
| 2018-01-25 00:00:00.000 | 0 |
| 2018-01-26 00:00:00.000 | 0 |
| 2018-01-27 00:00:00.000 | 0 |
| 2018-01-28 00:00:00.000 | 0 |
| 2018-01-29 00:00:00.000 | 0 |
| 2018-01-30 00:00:00.000 | 0 |
| 2018-01-31 00:00:00.000 | 0 |
| 2018-02-01 00:00:00.000 | 0 |
| 2018-02-02 00:00:00.000 | 0 |