Specifically looking for SQL Server (or ideally ANSI SQL) solutions. I wrote a recursive CTE solution for this but it does not perform remotely well for larger data sets.
First, I'll set the stage for the specific problem I'm trying to solve. But ultimately what I think is needed here is what I've described in the title (and in the final paragraph below).
Imagine you have a fact table with two columns - person_id and event_time. You want to partition by person_id and then order by event_time within each partition. Then, you want to start from the earliest time and move forward, each time tossing out anything within three minutes of the earliest time. When you reach something outside of that three minute window, the three minute window resets itself.
So for example:
CREATE TABLE #res (person_id int, event_time datetime)
INSERT INTO #res VALUES
(2,'9/28/2021 10:00 am'),
(2,'9/28/2021 10:01 am'),
(2,'9/28/2021 10:02 am'),
(2,'9/28/2021 10:03 am'),
(2,'9/28/2021 10:05 am'),
(2,'9/28/2021 10:07 am'),
(2,'9/28/2021 10:08 am'),
(2,'9/28/2021 10:09 am'),
(2,'9/28/2021 10:45 am');
In this example, we start with 10:00, but then throw out 10:01-10:03 because they are still within three minutes. Then we keep 10:05 and start counting from there. 10:07 and 10:08 are both within three minutes of 10:05, so we throw them out. Then we keep 10:09 and keep 10:45. We're left with:
(2,'9/28/2021 10:00 am'),
(2,'9/28/2021 10:05 am'),
(2,'9/28/2021 10:09 am'),
(2,'9/28/2021 10:45 am');
I feel like this problem fundamentally requires some level of self-reference and therefore requires a recursive CTE (or recursion in a function or procedure) but I was hoping someone could come up with another solution that I'm missing. Or even just tips for making something like this perform on a decent-sized fact table with somewhere between 100k and 1m rows.
What I really would like to be able to do here is carry forward a value from row to row that I can reference and then adjust - set it as 10:00 in this example, and then reset it once we get to a row that is outside of the 3 minute window (10:05). I don't think there's a way to achieve that kind of self-reference outside of the kinds of things I mentioned in the previous paragraph but I would very much like to be wrong.