excel - SQL Group Data within the date difference of 90 seconds

Question

I have sample data like

Element	Time Stamp
A	21/12/2020 06:10:56
B	21/12/2020 06:05:27
B	21/12/2020 06:06:10
A	21/12/2020 06:11:27
A	21/12/2020 06:05:27
A	21/12/2020 06:06:20
A	21/12/2020 06:12:30

Answer 1

If I understand correctly, you want "islands" that are separated by 90 seconds. If so, you can use lag() and a cumulative sum with aggregation:

select element, min(timestamp), max(timestamp), count(*)
from (select t.*,
             sum(case when prev_timestamp > dateadd(second, -90, timestamp) then 0 else 1 end) over (partition by element order by timestamp) as grp
      from (select t.*,
                   lag(timestamp) over (partition by element order by timestamp) as prev_timestamp
            from t
           ) t
     ) t
group by grp, element;

Here is a db<>fiddle.