SQL to determine minimum sequential days of access?

The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per user. If the user has not accessed the website for that day, no record will be generated.

What I’m looking for is a SQL query on this table with good performance, that tells me which userids have accessed the website for (n) continuous days without missing a day.

In other words, how many users have (n) records in this table with sequential (day-before, or day-after) dates? If any day is missing from the sequence, the sequence is broken and should restart again at 1; we’re looking for users who have achieved a continuous number of days here with no gaps.

Any resemblance between this query and a particular Stack Overflow badge is purely coincidental, of course.. 🙂

Add Comment
1 Answer(s)

The answer is obviously:


Okay here’s my serious answer:


[Jeff Atwood] This is a great fast solution and deserves to be accepted, but Rob Farley’s solution is also excellent and arguably even faster (!). Please check it out too!

Answered on April 6, 2016.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.