How to count records in given interval?

Question

I have a table with created column. I need to check if there at least one record that was created less then a minute ago, from last record with same user_id. E.g.

|            created|user_id|
|2020-01-01T22:22:22|      1|
|2020-01-01T22:22:44|      1|

^ Matches my clause. Created in one minute interval.

|            created|user_id|
|2020-01-01T22:22:22|      1|
|2020-01-01T22:24:44|      1|

^ Not matches, created in more than one minute interval.

Answer 1

You can use distinct on and window functions:

select distinct on (user_id) 
    user_id,
    created_at 
        <= lag(created_at) over(partition by user_id order by created_at) + interval '1' minute as flag
from mytable
order by user_id, created_at desc

This gives you one record per user_id, with a boolean column called flag that indicates whether the last but one created_at is one minute older or less than the latest created_at.

Demo on DB Fiddle.

Sample data:

created_at          | user_id
:------------------ | ------:
2020-01-01 22:22:22 |       1
2020-01-01 22:22:44 |       1
2020-01-01 22:22:22 |       2
2020-01-01 22:24:44 |       2

Results:

user_id | flag
------: | :---
      1 | t   
      2 | f