Lukas Eder discusses window functions:

Now, let’s assume I’m interested in these things:

- How many payments were there
in the same hour asany given payment?- How many payments were there
in the same hour beforeany given payment?- How many payments were there
within one hour beforeany given payment?Those are three entirely different questions.

Lukas’s solution uses Oracle syntax, but most of it also applies to SQL Server 2012 and higher. The part that doesn’t apply, unfortunately, is the RANGE BETWEEN INTERVAL, which allows you to find values clustered in the same time period (one hour in his example).