1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Drupal developers and administrators

---the query returns also questions which
---were closed at duplicates at some point
---and then later reopened
; with tagsduplstats_cte as
(
SELECT
t.TagName AS TagName,
Count(DISTINCT q.Id) AS ClosedCount
FROM Posts q INNER JOIN PostHistory ph ON ph.PostId=q.Id
  INNER JOIN PostTags pt ON q.Id = pt.PostId
  INNER JOIN Tags t ON pt.TagId = t.Id
WHERE (q.PostTypeId = 1)
  AND (ph.PostHistoryTypeId=10) AND (ph.Comment IN (1,101))
--- Old close reasons:
--- 1 = Exact Duplicate
--- Current close reasons:
--- 101 = Duplicate
  AND (q.CreationDate >= '##Date1?2010-01-01##') AND (q.CreationDate <= '##Date2?2030-01-01##')
GROUP BY t.TagName
), tagstats_cte as
(
SELECT
t.TagName AS TagName,
Count(q.Id) AS QuestionCount
FROM Posts q
  INNER JOIN PostTags pt ON q.Id = pt.PostId
  INNER JOIN Tags t ON pt.TagId = t.Id
WHERE (q.PostTypeId = 1)
  AND (q.CreationDate >= '##Date1?2010-01-01##') AND (q.CreationDate <= '##Date2?2020-01-01##')
GROUP BY t.TagName
)


SELECT
  ts.TagName,
  ROUND((CAST(td.ClosedCount AS decimal))*100.0/(CAST(ts.QuestionCount AS decimal)),2) AS percentage,
  td.ClosedCount, ts.QuestionCount
FROM tagstats_cte ts INNER JOIN tagsduplstats_cte td ON ts.TagName=td.TagName
WHERE ts.QuestionCount>=##num?1000##
ORDER BY ROUND((CAST(td.ClosedCount AS decimal))*100.0/(CAST(ts.QuestionCount AS decimal)),2) DESC

fork query

created nov 20 Martin20528