1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional mathematicians

; with tagsduplstats_cte as
(
SELECT
t.TagName AS TagName,
Count(DISTINCT q.Id) AS DuplCount
FROM Posts q INNER JOIN PostLinks pl ON pl.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 (pl.LinkTypeId=3)
---LinkTypeId type of link
---1 = Linked (PostId contains a link to RelatedPostId)
---3 = Duplicate (PostId is a duplicate of RelatedPostId)
  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,
Count(q.ClosedDate) AS ClosedCount
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?2030-01-01##')
GROUP BY t.TagName
)

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

fork query

edited nov 20 20

created nov 20 20 Martin20528