0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Joomla! administrators, users, developers and designers

select Posts.Id As [Post Link], CloseReasonTypes.Name As ClosureReason, Posts.Score, Posts.ViewCount,
--Filter out the start of the closure text
substring(CloseHistory.Text,CharIndex('"Voters":',CloseHistory.Text)+10,300) As ClosureText,
datediff(day, Users.CreationDate, CloseHistory.CreationDate) As AccountAgeDays

from Posts
Inner Join PostHistory As CloseHistory
on CloseHistory.PostId = Posts.Id
Inner Join PostHistory As ReopenHistory
on ReopenHistory.PostId = Posts.Id
Inner Join CloseReasonTypes
on CloseReasonTypes.Id = CloseHistory.Comment
Inner Join Users
on Users.Id = Posts.OwnerUserId

where CloseHistory.PostHistoryTypeId = 10 --10=close
and ReopenHistory.PostHistoryTypeId = 11 --11=reopen
--Quick check that we didn't get a second closure in CloseHistory
and Posts.ClosedDate Is Null

and (
  --Quality criteria to eliminate bad questions that deserved closure
  
  Posts.Id Not In (
    --Questions that were not edited before being reopened
    select EditHistory.PostId
    From PostHistory As EditHistory
    where EditHistory.PostId = Posts.Id
    and EditHistory.PostHistoryTypeId = 5
    and (EditHistory.CreationDate < ReopenHistory.CreationDate
    and EditHistory.CreationDate > CloseHistory.CreationDate)
  ) or
  (
    Posts.Id In (
      --Questions with at least two answers before closure can't be that bad
      Select AnswerPosts.ParentId
      From Posts As AnswerPosts
      Where AnswerPosts.CreationDate < (
        Select top 1 PostHistory.CreationDate
        From PostHistory
        Where PostHistory.PostHistoryTypeId = 10
        and PostHistory.PostId = AnswerPosts.ParentId
      )
      Group By AnswerPosts.ParentId
      Having Count(*) >= 2
    
    ) --Except duplicates do often get several answers even if they're really duplicates
    and CloseHistory.Comment != 101
  ) or
  --Well-received questions
  --These scores each would put a post in the top ~3% in Aviation SE
  --Admittedly a popular question can be very broad or unclear, but
  --it's very cynical IMO to completely shut down a top 3% question 
  --because it doesn't quite fit the rules
  --Closing a well-received question can also be a sign of hive-mind closures
  Posts.Score > 26 or Posts.ViewCount > 9800
)
Order By Posts.Score Desc

fork query

edited apr 25 19 anonymous

created apr 24 19 Cody.P