Closed Question Info

0

Please login or register to vote for this query.

(click on this box to dismiss)

Worldbuilding

Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

-- Note: for posts closed more than twice, re-open dates may not be correct.

SELECT p.id as [Post Link],
       u.id as [User Link],
       u.reputation Rep,
       --datediff(day, u.creationDate, getDate()) UserDaysOld, -- age of user (days since joined)
       datediff(day, u.creationDate, p.creationDate) DaysOldWhenAsked, -- difference in age between user and question
       p.creationDate Asked,
       ph.creationDate Closed,
       c.name Reason,
       phh.creationDate Reopened,
       p.tags

       
FROM   posts p
JOIN   posthistory ph
ON     p.id = ph.postid
JOIN   closereasontypes c
ON     ph.comment = c.id
JOIN   users u
ON     p.ownerUserId = u.id
LEFT JOIN posthistory phh 
ON     p.id = phh.postId
AND    phh.postHistoryTypeId = 11 --reopened
and    phh.creationDate - ph.creationDate > 0 -- reopened after close date
WHERE  ph.postHistoryTypeId = 10 -- closed
AND    p.postTypeId = 1 -- question


order by p.creationDate desc, ph.creationDate desc;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results