0

Please login or register to vote for this query.

(click on this box to dismiss)

Questions that have gone through a close/reopen cycle at least once.

Q&A about the site for people organizing, marketing or licensing open source development projects

-- Community Controversy
-- Questions that have gone through a close/reopen cycle at least once and have a score of at least 100

SELECT
    PostId AS [Post Link],
    CloseCount,
    ReopenCount,
    CurrentlyClosed
    FROM
    (
        SELECT
            Id AS PostId,
            Score AS Score,
            (CASE WHEN ClosedDate IS NULL THEN 0 ELSE 1 END) AS CurrentlyClosed,
            (SELECT COUNT(*) FROM PostHistory closed WHERE closed.PostId = p.Id AND closed.PostHistoryTypeId = 10) AS CloseCount,
            (SELECT COUNT(*) FROM PostHistory opened WHERE opened.PostId = p.Id AND opened.PostHistoryTypeId = 11) AS ReopenCount
            FROM Posts p
            WHERE p.PostTypeId = 1
    ) a
    WHERE CloseCount > 0
    ORDER BY
        (CloseCount + ReopenCount) DESC,
        CloseCount DESC,
        ReopenCount DESC

fork query

edited jul 17 12 David Zaslavsky

created dec 29 10 Jon Seigel