Percentage of Closed Questions by Tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Get the percentage of closed questions for each tag, excluding duplicates. You can set the minimum tag popularity with a parameter.

Mathematics

Q&A for people studying math at any level and professionals in related fields

-- Bugs:
-- Questions count as closed even after they are reopened.
--    (The query just checks ClosedDate.)
-- Questions that are closed more than once inflate the closed count.
--    [james-bond] on scifi.SE shows up as 150% closed because of this.


-- MinTotal: Min question count
DECLARE @min_total INT = ##MinTotal:INT?1##;
-- MaxTotal: Max question count "0 to disable"
DECLARE @max_total INT = ##MaxTotal:INT?10##;

SELECT *
FROM
  (
    SELECT
      t.TagName,
      Count(TagName) as Closed,
      t.Count as Total,
      ROUND(100.*Count(TagName)/t.Count,3) as [% Closed]
      FROM Posts p
      INNER JOIN PostTags pt ON p.Id = pt.PostId
      INNER JOIN Tags t ON pt.TagId = t.Id
      INNER JOIN PostHistory h ON p.Id = h.PostId
      WHERE
        p.PostTypeId = 1                  -- match questions
        AND p.ClosedDate IS NOT NULL      -- that have been closed at some point
        AND h.PostHistoryTypeId = 10      -- and have a history entry
        AND h.Comment NOT IN ('1', '101') -- that doesn't say it's a duplicate
      GROUP BY TagName, t.Count
  ) a
WHERE a.Total >= @min_total
      AND (a.Total <= @max_total OR @max_total < 1)
ORDER BY
    [% Closed] desc, TagName

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results