SQL-Only Counting Records Sufficiently Spaced apart — Three Solutions

A nice SQL challenge was presented to me by a colleague. The challenges basically consisted of this table. A table contains records that describe login events. Each record has a login timestamp and the identifier of the person logging in. The challenge is to count “unique” login events. These have been defined as unique, non-overlapping two hour periods in which a person has logged in. Such as period starts with a login that is at least two hours after the start of a previous period for that same person.

Visually this can be shown like this:

Login records on a timeline — illustrating the desired logic: only count initial login records if they are spaced at least two hours apart

EDIT 4th March 2021: Solution

You may have read this article before — when it read a litle differently. I have made substantial changes to the article because it originally contained an incorrect solution. A solution that I did not test properly (!) and that I had not peer reviewed — before publishing it for all to see. One person who saw, read and carefully checked the article was Iudith Mentzel (see the first comment) and I owe them a great debt. They gently pointed out my flawed logic and went so far as to provide a correct solution. And not just one solution — but three.

You are now reading a rewrite of the original article — based on Iudith’s solutions for which I can take no credit (apart…