# SQL to determine minimum sequential days of access ?

SQL to determine minimum sequential days of access ?

Asked on December 17, 2018 in

Have a look at this solution,

```SELECT DISTINCT UserId
FROM UserHistory uh1
WHERE (
SELECT COUNT(*)
FROM UserHistory uh2
WHERE uh2.CreationDate
BETWEEN uh1.CreationDate AND DATEADD(d, @days,     uh1.CreationDate)
) = @days OR UserId = 52551
```

We can also do as follows:

```DECLARE @days int
DECLARE @seconds bigint
SET @days = 30
SET @seconds = (@days * 24 * 60 * 60) - 1
SELECT DISTINCT UserId
FROM (
SELECT uh1.UserId, Count(uh1.Id) as Conseq
FROM UserHistory uh1
BETWEEN uh1.CreationDate AND
AND uh1.UserId = uh2.UserId
GROUP BY uh1.Id, uh1.UserId
) as Tbl
WHERE Conseq >= @days
```

That can be done much simpler,

```WITH numberedrows
AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID
ORDER BY CreationDate)
- DATEDIFF(day,'19000101',CreationDate) AS TheOffset,
CreationDate,
UserID
FROM tablename)
SELECT MIN(CreationDate),
MAX(CreationDate),
COUNT(*) AS NumConsecutiveDays,
UserID
FROM   numberedrows
GROUP   BY   UserID,
TheOffset
```

We’re looking for a range that has a consistent offset.  Because being that if we have list of the days (as a number), and a row_number, then missed days make the offset between these two lists slightly bigger. So

Also have use something called “ORDER BY NumConsecutiveDays DESC” at the end of this, or say “HAVING count(*) > 14” for a threshold…

Keep in mind that it is works in SQL2005 and on, helped by an index on tablename

Here , we are using TheOffset. Because the Turns out Offset is a reserved word.

There is better to use COUNT(*) is very valid – Here it shold have done that in the first place but wasn’t really thinking. but Previously instead of using this, it was using datediff(day, min(CreationDate), max(CreationDate))

The solution for determine minimum sequential days of access in sql is very simple, try this

```SELECT users.UserId, count(1) as cnt
FROM users
WHERE users.CreationDate > now() - INTERVAL 30 DAY
GROUP BY UserId
HAVING cnt = 30
```
 1 2 3 4 5 6 7 8 9 10 `WITH` `  ``-- This table contains all the distinct date ` `  ``-- instances in the data set` `  ``dates(``date``) ``AS` `(` `    ``SELECT` `DISTINCT` `CAST``(CreationDate ``AS` `DATE``)` `    ``FROM` `Posts` `    ``WHERE` `OwnerUserId = ##UserId##` `  ``)` `...`
``````SELECT users.UserId, count(1) as cnt