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?

  • 1
    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, 2017 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, 2017 at 23:39

1 Answer 1


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 network profile.

Notice the join on the posthistory table twice. That is done so the query plan includes the only non-clustered index on the posthistory table. Without using that index, SQL Server does an index scan and PostHistory has too many rows to complete all that scanning in under 2 minutes. Hence the trickery.

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 ph2.revisionguid 
      , ph2.posthistorytypeid
from ' + quotename(name) + '.dbo.posthistory ph
inner join ' + quotename(name) + '.dbo.users u 
   on u.id =  ph.userid 
   and u.accountid = ' + cast(@accountid as nvarchar) +' 
inner join ' + quotename(name) + '.dbo.posthistory ph2 
   on ph.id = ph2.id
where ph.posthistorytypeid between 4 and 6 )  as data
   for posthistorytypeid in ([4] , [5], [6]) 
) as pvt
) alldata
from sys.databases
where database_id > 5

-- 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.
Use the neat SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

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

You must log in to answer this question.

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