Fill in Event Based Gaps

Question

I am trying to calculate the outflow of clients based on the activity that they could do, against the outflow by date, which is a normal thing. We have events related to a specific host, in my example all the events are posted by Alice, but these can be different hosts.

All people who follow a particular event should be placed in the category (new, active, whipped and resurrected).

New one . The first time a person follows an event from a specific host.
Active . Follow it (and the last event from a specific host was also executed).
Churned : The follower had the opportunity to follow, but did not. Resurrected . The follower who hacked started following the previous host.

declare @events table (event varchar(50), host varchar(50), date date)
declare @eventFollows table (event varchar(50), follower varchar(50))

insert into @events values ('e_1', 'Alice', GETDATE())
insert into @events values ('e_2', 'Alice', GETDATE())
insert into @events values ('e_3', 'Alice', GETDATE())
insert into @events values ('e_4', 'Alice', GETDATE())
insert into @events values ('e_5', 'Alice', GETDATE())

insert into @eventFollows values ('e_1', 'Bob') --new
insert into @eventFollows values ('e_2', 'Bob') --active
--Bob churned 
insert into @eventFollows values ('e_4', 'Megan') --new 
insert into @eventFollows values ('e_5', 'Bob') --resurrected
insert into @eventFollows values ('e_5', 'Megan') --active 

select * from @events
select * from @eventFollows

The expected result should be something like this

select 'e_1', 1 as New, 0 as resurrected, 0 as active, 0 as churned --First time Bob follows Alice event
union all
select 'e_2', 0 as New, 0 as resurrected, 1 as active, 0 as churned --Bob follows the next event that Alice host (considered as Active)
union all
select 'e_3', 0 as New, 0 as resurrected, 0 as active, 1 as churned --Bob churns since he does not follow the next event 
union all
select 'e_4', 1 as New, 0 as resurrected, 0 as active, 0 as churned --First time Megan follows Alice event
union all
select 'e_5', 0 as New, 1 as resurrected, 1 as active, 0 as churned --Second time (active) for Megan and Bob is resurrected

I started by asking for something like below, but the problem is that I am not getting all the events that followers did not follow (but could follow).

select a.event, follower, date, 
    LAG (a.event,1) over (partition by a.host, ma.follower order by date) as lag, 
    LEAD (a.event,1) over (partition by a.host, ma.follower order by date) as lead,
    LAG (a.event,1) over (partition by a.host order by date) as lagP, 
    LEAD (a.event,1) over (partition by a.host order by date) as leadP
from @events a left join @eventFollows ma on ma.event = a.event order by host, follower, date

Any ideas?

Answer 1

This may seem like a slightly indirect approach, but you can discover the islands by checking the spaces in the numbers:

;with nrsE as
(
    select *, ROW_NUMBER() over (order by event) rnrE from @events
), nrs as
(
    select  f.*,host, rnrE, ROW_NUMBER() over (partition by f.follower, e.host order by f.event ) rnrF
    from nrsE e
    join @eventFollows f on f.event = e.event
), f as
(
    select host, follower, min(rnrE) FirstE, max(rnrE) LastE, ROW_NUMBER() over (partition by follower, host order by rnrE - rnrF) SeqNr 
    from nrs
    group by host, follower, rnrE - rnrF --difference between rnr-Event and rnr-Follower to detect gaps
), stat as   --from the result above  on there are several options. this example uses getting a 'status' and pivoting on it
(
    select e.event, e.host, case when f.FirstE is null then 'No participants' when f.LastE = e.rnrE - 1 then 'Churned' when rnrE = f.FirstE then case when SeqNr = 1 then 'New' else 'Resurrected' end else 'Active' end Status
    from nrsE e
    left join f on e.rnrE between f.FirstE and f.LastE + 1 and e.host = f.host
)
select p.* from stat pivot(count(Status) for Status in ([New], [Resurrected], [Active],  [Churned])) p

2 , ""

Answer 2

SELECT 
    X.event, X.host, X.date,
    IsNew =  SUM(CASE WHEN X.FirstFollowerEvent = X.event THEN 1 ELSE 0 END),
    IsActive = SUM(CASE WHEN X.lagFollowerEvent = X.lagEvent THEN 1 ELSE 0 END),
    IsChurned = SUM(CASE WHEN X.follower IS NULL THEN 1 ELSE 0 END),
    IsResurrected = SUM(CASE WHEN X.lagFollowerEvent <> X.lagEvent AND X.FirstFollowerEvent IS NOT NULL THEN 1 ELSE 0 END)
FROM 
    (
    select 
        a.event, a.host, ma.follower, a.date, 
        FIRST_VALUE(a.event) over (partition by a.host, ma.follower order by a.date, a.event) as FirstFollowerEvent, 
        LAG (a.event,1) over (partition by a.host, ma.follower order by a.date, a.event) as lagFollowerEvent, 
        LAG (a.event,1) over (partition by a.host order by a.date, a.event) as lagEvent
    FROM
         @events a
         LEFT join 
         @eventFollows ma on a.event = ma.event
    ) X
GROUP BY
    X.event, X.host, X.date
ORDER by 
    X.event, X.host, X.date