Is there a way to find out how many edits I've made, network-wide?

Question

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?

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
N'
select ''' + name + '''
     , sum([4]) as [title]
     , sum([5]) as [body]
     , sum([6]) as [tag]
     , count(*) as [revtot]
from 
(
select revisionguid 
     , [4] 
     , [5]
     , [6]
 from
(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
pivot
(
   count(posthistorytypeid)
   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:

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.