CODEWITHSUNDEEP
sqlpostgresql
Blink Rankin
Blink Rankin

Reputation: 233

Query and return user requests if dates are consecutive

I am attempting to group records together by consecutive dates in the request_date column and user field but only return if the count is equal or above a certain number, say 3.

At the moment the Columns I have would be 

user_id | request_date |
--------|--------------|
3       | 2019-01-01   | 
5       | 2019-05-08   | 
3       | 2019-01-02   | 
4       | 2019-08-09   |
3       | 2019-01-03   |

the query would ideally return something along the lines of:

user_id: 3
num_of_reqs: 3
first_date: 2019-01-01
last_date: 2019-01-03

any insight would be appreciated.

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

You can use window functions. In particular, subtracting an increasing sequence from the date column will be constant when the dates are consecutive.

Something like this:

select user_id, count(*) as num_requests,
       min(request_date), max(request_date)
from (select t.*,
             row_number() over (partition by user_id order by request_date) as seqnm
      from t
     ) t
group by user_id, (request_date - seqnum)

If you want to limit to a particular number, then add a having clause:

having count(*) >= 3

for instance.

Upvotes: 1

Related Questions