SQL to determine individual periods of consecutive access days?

Question

Jeff recently asked this question and received excellent answers.

Jeff's problem came down to finding users who had (n) consecutive days when they logged in. Using the database table structure as follows:

Id UserId CreationDate
------ ------ ------------
750997 12 2009-07-07 18: 42: 20.723
750998 15 2009-07-07 18: 42: 20.927
751000 19 2009-07-07 18: 42: 22.283

Read the original question for clarity, and then ...

I was intrigued by the problem of determining the number of different (n) -day periods for a user.

Is it possible to create a quick SQL query that could return a list of users and the number of separate (n) -day periods that they have?

EDIT : as per the comment below. If someone has 2 consecutive days, then a space, then 4 consecutive days, then a space, then 8 consecutive days. This will be 3 "reporting 4-day periods." The 8-day period should be considered two periods of 4 parties back.

Answer 1

My answer did not seem to appear ...

I'll try again...

Rob Farley answers the original question, has a convenient advantage, including the number of consecutive days.

with numberedrows as
(
        select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
        from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset

, " (n) - ", ...
- 2/4 = 0
- 4/4 = 1
- 8/4 = 2
- 9/4 = 2
- etc, etc

, ...
( , , !)

with
    numberedrows (
        UserID,
        TheOffset
    )
as
(
    select
        UserID,
        row_number() over (partition by UserID order by CreationDate)
            - DATEDIFF(DAY, 0, CreationDate) as TheOffset
    from
        tablename
),
    ConsecutiveCounts(
        UserID,
        ConsecutiveDays
    )
as
(
    select
        UserID,
        count(*) as ConsecutiveDays
    from
        numberedrows
    group by
        UserID,
        TheOffset
)
select
    UserID,
    SUM(ConsecutiveDays / @period_length) AS distinct_n_day_periods
from
    ConsecutiveCounts
group by
    UserID

, Rob, GROUP BY...

Answer 2

- , . userid NumConsecutiveDays, , .

with numberedrows as
(
        select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
        from tablename
)
,
runsOfDay as
(
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset
)
select UserID, NumConsecutiveDays, count(*) as NumOfRuns
from runsOfDays
group by UserID, NumConsecutiveDays
;

, , , , " NumConsecutiveDays >= @days".

, 16 5- , NumConsecutiveDays/@runlength ( ). , , , , . SUM (NumOfRuns * NumConsecutiveDays/@runlength), , .

with numberedrows as
(
        select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
        from tablename
)
,
runsOfDay as
(
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset
)
select UserID, sum(NumConsecutiveDays / @runlength) as NumOfRuns
from runsOfDays
where NumConsecutiveDays >= @runlength
group by UserID
;

, ,

Rob

Answer 3

, .

DECLARE @days int
SET @days = 30

SELECT DISTINCT l.UserId, (datediff(d,l.CreationDate, -- Get first date in contiguous range
(
    SELECT min(a.CreationDate ) as CreationDate
    FROM UserHistory a
        LEFT OUTER JOIN UserHistory b 
            ON a.CreationDate = dateadd(day, -1, b.CreationDate ) AND
            a.UserId = b.UserId
    WHERE b.CreationDate IS NULL AND
        a.CreationDate >= l.CreationDate AND
        a.UserId = l.UserId
) )+1)/@days as cnt
INTO #cnttmp
FROM UserHistory l
    LEFT OUTER JOIN UserHistory r 
        ON r.CreationDate = dateadd(day, -1, l.CreationDate ) AND
        r.UserId = l.UserId
WHERE r.CreationDate IS NULL
ORDER BY l.UserId

SELECT UserId, sum(cnt)
FROM #cnttmp
GROUP BY UserId
HAVING sum(cnt) > 0