What are my highest activity streaks?

Question

I have written the following query to figure out activity streaks on a per-user basis. I find it... Ugly... And would love to improve it!

Limitations

Those are explained as commented text at the very top of the query.

Problems

• Can this be done without a loop?

• Is there a better way to do this than #TempTables?

Bonus!

If it is possible to do this for multiple users, e.g., a top 10 or top 50, I'll award a 100 rep bounty to whoever solves it first!

/*
There are certain limitations to the usefulness of this query.
It cannot track if a user has just visited the site without "significant" activity
"Significant" would be defined as a Comment, Post, Suggest edit, Approve or Reject edit, Vote on suggested edit, Vote on a post
Also, Up-votes and Down-votes on Posts are anonymous, as such they cannot be included in the query
*/

DECLARE @UserId INT;
SET @UserId = ##UserId##;

-- Temp table to hold all relevant activity dates
CREATE TABLE #UserActiveDays
(
    UserId INTEGER
  , ActivityDate DATE
  , Source VARCHAR(50)
);

INSERT INTO #UserActiveDays
(
    UserId
  , ActivityDate
  , Source
)
  SELECT DISTINCT
      OwnerUserId
    , CAST(CreationDate AS DATE)
    , 'PostCreation'
  FROM Posts
  WHERE OwnerUserId = @UserId
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'CommentCreation'
  FROM Comments
  WHERE UserId = @UserId
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'PostHistoryCreation'
  FROM PostHistory
  WHERE UserId = @UserId
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(CreationDate AS DATE)
    , 'SuggestedEditCreation'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(ApprovalDate AS DATE)
    , 'SuggestedEditApproval'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(RejectionDate AS DATE)
    , 'SuggestedEditRejection'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'SuggestedEditVote'
  FROM SuggestedEditVotes
  WHERE UserId = @UserId
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'VoteCreation'
  FROM Votes
  WHERE UserId = @UserId;

CREATE TABLE #ConsecDaysCount 
( 
    UserId INT
  , DaysCount INT
  , DateStreakEnded DATE
);

DECLARE @FirstDay DATE;
  SET @FirstDay = ( SELECT MIN(CreationDate) FROM Users WHERE Id = @UserId );
DECLARE @LastDay DATE;
  SET @LastDay = ( SELECT MAX(LastAccessDate) FROM Users WHERE Id = @UserId );
DECLARE @ConsecDaysCounter INT;
  SET @ConsecDaysCounter = 0;

WHILE (@FirstDay < @LastDay)
BEGIN
  IF ( SELECT DATEADD(DAY, 1, @FirstDay) ) IN (SELECT ActivityDate FROM #UserActiveDays)
    BEGIN
    SET @ConsecDaysCounter = @ConsecDaysCounter + 1;
    SET @FirstDay = DATEADD(DAY, 1, @FirstDay);
    END;
  ELSE 
    BEGIN
    INSERT INTO #ConsecDaysCount (UserId, DaysCount, DateStreakEnded) 
      VALUES (@UserId, @ConsecDaysCounter, @FirstDay);
    SET @ConsecDaysCounter = 0;
    SET @FirstDay = DATEADD(DAY, 1, @FirstDay);
    END;
END;

SELECT
    UserId AS [User Link]
  , DaysCount
  , DateStreakEnded
FROM #ConsecDaysCount
ORDER BY DaysCount DESC;

Answer 1

General

There are a few issues in here that cause inaccurate data, as well as some inefficiencies.

1. When a post is created it is also registered in PostHistory (three times, actually) There is no need to check for post creation at all since you already check for the same activity in the history.
2. You populate the Source column, but never use it. SQL is in large part dependent on data volume, and you are 'shifting' more data than you need to.
3. Your VoteCreation section is OK, but you realize you only pull out bounties and favourites from that, right (VoteType 5 and 8)? Other votes are anonymized, and are not possible to find...
4. Somehow you have 0-day streaks. How does that make sense?

SuggestedEdits requires a special section....

You have three sections for SuggestedEdits. One uses the Creation date, the others use the Approved and Rejected dates. The approval and rejection are not activities by the Owner of the suggested edit, but you are counting them as such.... Only the CreationDate indicates activity by the OwnerUserId in the SuggestedEdit.

The approval and rejection activity are recorded in the SuggestedEditVotes table (which you process from a different section).

Note, you are missing 'accepts an answer' which can be calculated from the Post/Votes combination.

As for your 'additional questions'....

Without the temp table

Yes, this can be done without the temp table, and without the loop.

You can do it all as a single query, if you use CTE expressions to hold the data.

With a CTE containing the 'activity' data, you can find the first day in a streak (Using a self-join, does the user have activity on the previous day, if they do not, then it's the first day of a streak)

Once you have identified the first day, you can also identify which activity belongs in which streak (the most recent start before the current activity must be the start...)

Finally, the streak is as long as the furthest date that belongs to each streak....

As it happens, this is efficient enough to be run for all users on the whole of Code Review.... the query is (Here in SEDE):

declare @userid integer = ##UserId:int?-1##;

with ActiveDays as (

    select Distinct
           UserId as UserId,
           Convert(Date, CreationDate) as Day
    from   PostHistory
    
  UNION
  
    select Distinct
           UserId as UserId,
           Convert(Date, CreationDate) as Day
    from   Comments
    
  UNION
  
    select Distinct
           OwnerUserId as UserId,
           convert(Date, CreationDate) as Day
    from SuggestedEdits
    
  UNION
  
    select distinct
           Q.OwnerUserId as UserId,
           Convert(Date, V.CreationDate) as Day
    from Posts Q inner join Votes V
      on Q.AcceptedAnswerId = V.PostId
      and V.VoteTypeId = 1
      
  UNION
  
     select distinct
            UserId as UserId,
            Convert(Date, CreationDate) as Day
     from SuggestedEditVotes
    
), StreakStarts as (

    select UserId, Start.Day
    from ActiveDays Start
    where not exists (
        select 1
        from ActiveDays
        where Start.UserId = UserId
          and DateAdd(dd, -1, Start.Day) = Day
    )
    
), StreakMerge as (

    select A.UserId, A.Day, Max(S.Day) as Start
    from ActiveDays as A inner join StreakStarts as S
      on A.UserId = S.UserId
      and A.Day >= S.Day
    group by A.UserId, A.Day
    
), LongStreaks as (

    select UserId, Start, 1 + max (DateDiff(dd, Start, Day)) as  Streak
    from StreakMerge
    group by UserId, Start
)

Select U.Id as [User Link], U.DisplayName, Start, S.Streak
from Users as U inner join LongStreaks as S
  on U.Id = S.UserId
where S.Streak > 1
  and (@userid = -1 OR U.Id = @userid)
order by S.Streak desc

Answer 2

The query to populate #UserActiveDays should probably be simplified. You don't need the activity type. You can also aggregate everything, then filter by UserId and uniquify at the end. It would be roughly equivalent to the ActivityDates CTE in the query below.

Window functions, particularly LAG(), should be appropriate for analyzing consecutive entries in a table.

Most of the operations involve finding just the LEAD() and LAG() by 1, always keeping the same sort order. There is only one self-join, so the query should be quite efficient.

(I've stolen @rolfl's idea to add a mode in which to run the query for all users, but haven't fixed some of the bugs that he spotted in the original code.)

WITH Activities(UserId, Date) AS (
    SELECT OwnerUserId, CAST(CreationDate AS DATE)
      FROM Posts
    UNION ALL
    SELECT UserId, CAST(CreationDate AS DATE)
      FROM Comments
    UNION ALL
    SELECT UserId, CAST(CreationDate AS DATE)
      FROM PostHistory
    UNION ALL
    SELECT OwnerUserId, CAST(CreationDate AS DATE)
      FROM SuggestedEdits
    UNION ALL
    SELECT OwnerUserId, CAST(ApprovalDate AS DATE)
      FROM SuggestedEdits
      WHERE ApprovalDate IS NOT NULL
    UNION ALL
    SELECT OwnerUserId, CAST(RejectionDate AS DATE)
      FROM SuggestedEdits
      WHERE RejectionDate IS NOT NULL
    UNION ALL
    SELECT UserId, CAST(CreationDate AS DATE)
      FROM SuggestedEditVotes
    UNION ALL
    SELECT UserId, CAST(CreationDate AS DATE)
      FROM Votes
), ActivityDates AS (
    SELECT DISTINCT UserId, Date, DENSE_RANK() OVER (PARTITION BY UserId ORDER BY Date) AS Seq
      FROM Activities
), ConsecutiveDates AS (
    SELECT Later.UserId
         , Later.Date AS LaterDate, Later.Seq AS Seq
      FROM ActivityDates AS Later
        INNER JOIN ActivityDates AS Earlier
          ON Later.UserId = Earlier.UserId
          AND Later.Seq = Earlier.Seq + 1
      WHERE DATEADD(DAY, 1, Earlier.Date) = Later.Date
), StreakEndDetect AS (
    SELECT UserId
         , LaterDate
         , DENSE_RANK() OVER (PARTITION BY UserId ORDER BY LaterDate) AS Seq
         , CASE WHEN DATEADD(DAY, +1, LaterDate) <> LEAD(LaterDate, 1, '2999-12-31') OVER (PARTITION BY UserId ORDER BY Seq) THEN 1 ELSE 0 END AS StreakEnd
      FROM ConsecutiveDates
), StreakDetect AS (
    SELECT UserId, LaterDate, Seq
         , CASE WHEN LAG(StreakEnd, 1, 1) OVER (PARTITION BY UserId ORDER BY Seq) = 1 THEN 1 ELSE 0 END AS StreakStart
         , StreakEnd
      FROM StreakEndDetect
), Compressed AS (
    SELECT *
      FROM StreakDetect
      WHERE StreakStart = 1 OR StreakEnd = 1
), StreakCount AS (
    SELECT UserId
         , LaterDate AS EndDate
         , 1 + Seq - LAG(Seq, 1, 0) OVER (PARTITION BY UserId ORDER BY Seq) AS StreakLen
      FROM Compressed
      WHERE StreakEnd = 1
)
SELECT UserId AS [User Link]
     , EndDate AS [End Date]
     , StreakLen AS [Streak Length (Days)]
    FROM StreakCount
    WHERE
      UserId >= 0                          -- Ignore "Community" user
      AND StreakLen > 7                    -- Eliminate long tail
      AND ##UserId:int?0## IN (0, UserId)  -- Specify UserId=0 for all users
    ORDER BY StreakLen DESC, EndDate DESC, UserId;