User's activity streaks

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/*
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
*/

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
    AND CreationDate IS NOT NULL
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'CommentCreation'
  FROM Comments
  WHERE UserId = @UserId
    AND CreationDate IS NOT NULL
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'PostHistoryCreation'
  FROM PostHistory
  WHERE UserId = @UserId
    AND CreationDate IS NOT NULL
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(CreationDate AS DATE)
    , 'SuggestedEditCreation'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
    AND CreationDate IS NOT NULL
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(ApprovalDate AS DATE)
    , 'SuggestedEditApproval'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
    AND ApprovalDate IS NOT NULL
UNION
  SELECT DISTINCT
      OwnerUserId
    , CAST(RejectionDate AS DATE)
    , 'SuggestedEditRejection'
  FROM SuggestedEdits
  WHERE OwnerUserId = @UserId
    AND RejectionDate IS NOT NULL
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'SuggestedEditVote'
  FROM SuggestedEditVotes
  WHERE UserId = @UserId
    AND CreationDate IS NOT NULL
UNION
  SELECT DISTINCT
      UserId
    , CAST(CreationDate AS DATE)
    , 'VoteCreation'
  FROM Votes
  WHERE UserId = @UserId
    AND CreationDate IS NOT NULL;

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;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results