Count rows that follows other rows in a single table both restricted with a where clause

Question

I'm using SQL Server 2014.

I have a table that contains several millions of events. The primary key is composed of three columns:

• Time DateTime
• user (bigint)
• context (varchar(50))

I have another column with a value (nvarchar(max))

I need to count rows restricted on

context = 'somecontext' and value = 'value2'

that follows in time rows restricted on

context = 'somecontext' and value = 'value1'

for the same user.

For Example with the following records:

Time user context value

2019-02-22 14:56:57.710 359586015014836 somecontext value1

2019-02-22 15:13:42.887 359586015014836 somecontext value2 <------ Need to count this rows only.

It is "recorded" 15 min after the first one and the user and context are the same.

I have seen other similar questions like this one or that one.

Should I make a JOIN on the same table? Use subqueries? may be a CTE? I'm concerned about performance that should be optimal.

The idea would be to use query features available in this version of the DB engine.

Answer 1

If the example that I made in comment is what you want than you can use the following code assuming that you want to select all the rows where context = 'c1', current value = 'v1', next value = 'v3' if ordered by time:

declare @t table
(
    Time_ DateTime,
    user_ bigint,
    context varchar(50),
    value_ varchar(50)
);

insert into @t values
('20000101', 1, 'c1', 'v1'),
('20000102', 1, 'c2', 'v3'),
('20000103', 1, 'c1', 'v3'),
('20000104', 2, 'c1', 'v1'),
('20000105', 2, 'c1', 'v4'),
('20000106', 2, 'c1', 'v2');

with cte as
(
select *, 
       lead(value_) over(partition by user_ order by time_) as next_value
from @t
where context = 'c1' 
)

select *
from cte
where next_value = 'v3';