All Activity/Actions for specified User on specified site (3 tabs + Graph)

0

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'.)

Stack Overflow

Q&A for professional and enthusiast programmers

--All Activity/Actions for specified User
--Enter Userid in text box below (or your logged-in UserID will be used)

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], --offset +1ms so Posts.ownership is first
        '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 (later Approved)' end 
          else 'Edit (later 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

---or---

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

---or---

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) 

--the following line sets the SEDE prompt & tooltip for the UserID parameter.
--UserId: Which UserID should we query?  "Enter numeric UserID for this site (or leave blank to use yours!)"

Enter Parameters

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