What question has the most cycles of Close/Reopen?

Question

Just out of curiosity, which question has been closed and reopened the most times? What caused the closing and reopening war in the first place?

Answer 1

Here's a data explorer query that should answer your question.

It returns the following 3 posts:
Strangest language feature
What is the worst programming language you ever worked with?
How to pair socks from a pile efficiently?

These were pretty obviously closed/reopened so many times, because they're "fun" questions. Not terrifically useful, but they're good reads when you're killing time.

---

Correction...

The above query queries Stack Overflow, you'll have to switch sites to check other communities, but from what little digging I did, few other sites have posts that went through more than 5 cycles.

Answer 2

The per site query was nicely offered by apaul34208 so I decided to make the query that offers the networkwide result in one go:

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                      , postid int
                      , title nvarchar(250)
                      , cls int
                      , opn 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 + '''
     , id
     , title
     , [closed]
     , [re-opened]
from ' + quotename(name) + '.dbo.posts p
inner join (
select top 10 postid
     , [10] as [closed]
     , [11] as [re-opened]
     from ( select postid, posthistorytypeid 
            from ' + quotename(name) + '.dbo.posthistory
            where posthistorytypeid in (10,11) --close/reopen
           ) as src
pivot ( 
  count(posthistorytypeid)
  for posthistorytypeid
  in ([10], [11])
) as pvt
order by [10]+[11] desc
) ph on ph.postid = p.id
-- order  by [closed]+ [re-opened] desc
' 
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  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'
      ) 
      + '/q/'
      + cast(postid as nvarchar)
      + '|'
      + title 
      -- rest of columns
      site
      , [cls]
      , [opn]
from #results
order by [cls] + [opn] desc

drop table #results

I used the pivot query functionality of SQL Server to get the top 10 per site. You can can find the initial revisions of that query here which was used for debug purposes.

I also included all meta sites so you can follow along on the biggest drama's.

Here is the result when run today (remember that SEDE only refreshes once a week on Sunday).