I believe there are a few things that need further clarification:
What SQL Server version are you on? (2005, 2008R2, 2012, etc.)
What are you sorting by(order by)? Should all of the records in table_1 be in chronological order by ts, or something else?
What are you partitioning by(partition by)? If after getting table_1 sorted properly, should I be concerned with the number of seconds between the two ts values if user_id, or item_id values change? (Example: id 27 and 28 are 37 seconds apart, but should that matter considering that item_id is 8 for id 27 and item_id is 7 for id 28)
That said, I will give you a solution that will evaluate the example records of table_1 assuming 3 things:
That you are on SQL Server 2012 or newer.
That you do not care about any partition by functionality, and that you want to assume a NULL value for the first (or last) row where you do not have a second ts value to compare against.
That you want to sort by ts in chronological order.
; with lead as
, lead(a.ts, 1, NULL) over (order by a.sort_id asc) as ts_lead
from table_1 as a
, datediff(s, l.ts, l.ts_lead) as ts_diff
from lead as l
, count(*) as cnt
from diff as d
and d.ts_diff >= 10
Asked in February 2016Viewed 3,385 timesVoted 6Answered 1 times