All Activity/Actions for specified User (4 tabs + Graph)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Combined list of All Actions (aka All Activity) by specified user: Create post, Edit post, Account creation, Account last active, Comment, Badge won (not necessarily 'user action'), Post History actions, Suggested Edits (with result), Suggested Edit Votes and Votes (only 'favorites' available, for privacy). (This is the user activity available from SEDE. The sites likely track much more detail, but much is not shared for privacy and other reasons. Does NOT include creation/deletion of 'Post Notices', since I can't do that yet.)

Stack Overflow

Q&A for professional and enthusiast programmers

--All Activity/Actions for specified User
--Enter Userid in text box below

declare @tzo decimal
set @tzo = +1.0  --'time zone offset from UTC for Tab 4)(

Select 
  x.*, 
  lag(x.[Activity Date]) Over (Order By x.[Activity Date]) As [Last Activity Date]
Into #temp
From 
  (
      ( select -- POSTS 1/2 (CreationDate)
          CreationDate as [Activity Date],
          'Posts (Owner)' as [Table],
          t.name as [Record Type],
          case when parentid is null then p.id else p.parentid end [Post Link]
        from posts p left outer join posttypes t
        on p.posttypeid = t.id
        where OwnerUserId = ##UserId## )

 UNION ( select -- POSTS 2/2 (LastEditDate)
          LastEditDate as [Activity Date],
          'Posts (Editor)' as [Table],
          t.name as [Record Type],
          case when parentid is null then p.id else p.parentid end [Post Link]
        from posts p left outer join posttypes t
        on p.posttypeid = t.id
        where LastEditorUserId = ##UserId## )

 UNION ( select -- USERS 1/2 (CreationDate)
          CreationDate as [Activity Date],
          'Users' as [Table],
          'Account Created' as [Record Type],
          null as [Post Link]
        from users
        where id = ##UserId## )

 UNION ( select -- USERS 2/2 (LastAccessDate)
          LastAccessDate as [Activity Date],
          'Users' as [Table],
          'Account Last Accessed' as [Record Type],
          null as [Post Link]
        from users
        where id = ##UserId## )

 UNION ( select -- COMMENTS
          CreationDate as [Activity Date],
          'Comments' as [Table],
          null as [Record Type],
          PostId as [Post Link]
        from comments
        where UserId = ##UserId## )

 UNION ( select -- BADGES
          date as [Activity Date],
          'Badges' as [Table],
          name as [Record Type],
          null as [Post Link]
        from badges
        where UserId = ##UserId## )

 UNION ( select -- POST HISTORY
          dateadd(ms,1,creationdate) as [Activity Date],
        --creationdate offset +1ms so Ownership appears 1st
          'PostHistory' as [Table],
          name as [Record Type],
          PostId as [Post Link]
        from posthistory left outer join PostHistoryTypes h
        on PostHistoryTypeId = h.id  
        where UserId = ##UserId## )

 UNION ( select -- SUGGESTED EDITS
          CreationDate as [Activity Date],
          'SuggestedEdits' as [Table],
          case when rejectiondate is null 
            then case when approvaldate is null 
                 then 'Edit' 
                 else 'Edit (Approved)' end 
            else 'Edit (Rejected)' end as [Record Type],
          postid as [Post Link]
        from SuggestedEdits
        where OwnerUserId = ##UserId## )

 UNION ( select -- SUGGESTED EDIT VOTES
          sev.CreationDate as [Activity Date],
          'SuggestedEditVotes' as [Table],
          vt.name as [Record Type],
          se.postid as [Post Link]
        from suggestededitvotes sev
        left outer join votetypes vt
        on sev.votetypeid = vt.id
        left outer join suggestededits se
        on sev.SuggestedEditId = se.id
        where sev.UserId = ##UserId## )

 UNION ( select -- VOTES (contains only Fav's)
          CreationDate as [Activity Date],
          'Votes' as [Table],
          name as [Record Type],
          PostId as [Post Link]
        from votes left outer join votetypes v
        on votetypeid = v.id
        where UserId = ##UserId## )

  ) x Order By x.[Activity Date]
 --------------------------------------------------------------------------
SELECT 
  *, 
  cast(
    cast( datediff(ss,[Last Activity Date],[Activity Date]) as decimal) /3600 
   as decimal(9,3)) as [Hrs since Prev]
INTO #activityOut
FROM #temp 
ORDER BY [Activity Date]
 --------------------------------------------------------------------------

SELECT * -- RESULTS TAB #1 : All Actions
FROM #activityOut

 ---and---

select   -- RESULTS TAB #2 + GRAPH : Hours between Actions by Date (+GRAPH)
  format(cast([Activity Date] as date),'yyyy-MM-dd') as [Date],
  count([Activity Date]) as [# Actions],
  sum(case when [Hrs since Prev] <= 1 then 1 else 0 end) as [# Actions <1hr apart],
  max([Hrs since Prev]) as [Max HrsSincePrev]
from #activityOut
where [Hrs since Prev]<=144 --ignore gaps >6days
group by cast([Activity Date] as date)
order by cast([Activity Date] as date) DESC

 ---and---

select   -- RESULTS TAB #3 : "Hardcore Site User?" : Weekly usage frequency
         -- ...including "how often did you miss 24 hrs in a row?!"
  format(year([Activity Date]) + (cast(datepart(ww,[Activity Date]) as decimal(6,2)
          )/100),'0000.00') as [Year.Week],
  format(min([Activity Date]),'yyyy-MM-dd') as [Week Start],
  format(max([Activity Date]),'yyyy-MM-dd') as [Week End],
  count([Activity Date]) as [# Actions],
  sum(case when [Hrs since Prev] > 24 then 1 else 0 end) as [# Actions >24hr apart],
  max([Hrs since Prev]) as [Max HrsSincePrev]  
from #activityOut
group by
  year([Activity Date]) + (cast(datepart(ww,[Activity Date]) as decimal(6,2))/100) 
order by 
  year([Activity Date]) + (cast(datepart(ww,[Activity Date]) as decimal(6,2))/100) 


 ---and---

select  -- RESULTS TAB #3 : Usage by Hour of Day (set local TimeZone offset at top)
  cast(datepart(hh,dateadd(hh,@tzo,[Activity Date])) as varchar)+':00' as hr,
  count([Activity Date]) as numActivities
from 
  #activityOut
where 
  [Hrs since Prev] > 0
group by
  datepart(hh,dateadd(hh,@tzo,[Activity Date]))
order by
  datepart(hh,dateadd(hh,@tzo,[Activity Date]))


--the following line sets the SEDE prompt & tooltip for the UserID parameter.
--UserId: Which UserID should we stalk, er, query?  "Enter your numeric UserID for this site"

Enter Parameters

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