I'm a prolific editor. I've made edits on a whole bunch of different sites. I'd like to know exactly how many edits I've made, network-wide. Is there a way to find out, perhaps with SEDE?

  • There is a way to do it. I don't have time to write the SQL now, but it's possible, if convoluted, to query across all sites on Stack Exchange in SEDE. – user206222 Mar 5 '17 at 22:32
  • One could also theoretically code up something that uses the API, first using /users/{id}/associated to get a list of all accounts, then for each using /users/{id}/timeline and counting the revision items. Unfortunately the activity -> revisions page on the network profile limits the displayed items so that's not a route you can take. – Jason C Mar 5 '17 at 23:39
  • @JasonC - actually, I can't. But maybe someone else can :P – Mithical Mar 5 '17 at 23:40
  • @Mithrandir Fixed, ha. – Jason C Mar 5 '17 at 23:41

You can use SEDE for that if you don't mind the data can be up to 7 days behind.

For your purpose I've create this query that visits each database and then finds the rows in the posthistory table for events of type 4, 5 and 6 (title, body and tag edits). The aggregate of the per database result is stored and then in the end queried to form the final result. I didn't bother to filter out edits on your own posts but feel free to make the adjustments needed for a single site before you edit the change in your own fork.

The query needs your accountid, which you can find in the url of your networkprofile.

declare @accountid as int = ##accountid?5129611##

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , title int
                       , body int
                       , tag int
                       , revs int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
select ''' + name + '''
     , sum([4]) as [title]
     , sum([5]) as [body]
     , sum([6]) as [tag]
     , count(*) as [revtot]
select revisionguid
     , [4] 
     , [5]
     , [6]
(select revisionguid
      , posthistorytypeid
from ' + quotename(name) + '.dbo.posthistory ph
inner join ' + quotename(name) + '.dbo.users u on u.id =  ph.userid
where posthistorytypeid in (4,5,6)
and u.accountid = ' + cast(@accountid as nvarchar) +' )  as data
   for posthistorytypeid in ([4] , [5], [6]) 
) as pvt
) alldata
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results

select 'total' as site
     , sum(title) as title
     , sum(body) as body
     , sum(tag) as tag
     , sum(revs) as [#revisions]
from #results
where revs > 0
union all
select *
from #results
where revs > 0

drop table #results

When run today this is what the result will look like:

all edits networkwide

Keep in mind SEDE is only updated once a week, on Sunday.

| improve this answer | |
  • 'feel free to' - heh. I can't code for my life :P Thanks; this is awesome! – Mithical Jul 2 '17 at 18:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .