Never content: What is the most changed post?

Question

What post has the longest list of revisions on all SE sites? Where is this information available?

Answer 1

I didn't search other sites yet, but this post on Stack Overflow seems a winner to me. It has a whopping 753 revisions!

You can run this query on SEDE to get the results:

select PostId [Post Link]
,      count(*)
from   posthistory
where  posthistorytypeid in (4,5,6,7,8,9,24)
group
by     PostId
having count(*) > 100
order
by     count(*) desc

If you don't want to include rollbacks, you can exclude PostHistoryTypeId 7 to 9 (which are all rollback types). The most edited post seems to be this post on MSE.

Answer 2

The answer from Patrick is a reasonable indicator that doesn't hammer the SEDE database to much. It does report higher (sometimes way higher) counts for some posts then the actual /revisions page will show you. This is caused by the fact that some title, body and tag events will be rolled up in one revision, indicated by the same revisionguid, The following query examines all available databases in SEDE and counts the actual revisions per post, including tag wikis:

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , post int
                       , title nvarchar(250)
                       , revcnt 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 + '''
     , postid
     , (select (case when q.title is null and q.parentid is not null then 
                  ( select title 
                   from ' + quotename(name) + '.dbo.posts mq 
                   where mq.id = q.parentid)
               else title
               end) as qtitle
        from ' + quotename(name) + '.dbo.posts q
        where id = postid ) as nqtitle
     , count(*) as cnt
from (select postid
           , revisionguid
      from ' + quotename(name) + '.dbo.posthistory ph 
      where ph.posthistorytypeid < 10 -- all edits
      group by postid
             , revisionguid
      ) as data
group by postid
having count(*) > 10
'
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 top 10000
       site
       , 
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + '/posts/'
      + cast(post as nvarchar) 
      + '/revisions'
      + '|'
      + case when title is null 
             then 'tag wiki:' + cast(post as nvarchar) 
             else title
        end as [Post]
      -- rest of columns
      , revcnt as [Number of revisions]
from #results
order by revcnt desc

drop table #results

When run today the result looks like this:

For a per database query you can use this version