Percentage of deleted questions per tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

---we cannot use PostTags table - since we want data about DELETED question
---similar query - but only existing tags: https://data.stackexchange.com/mathoverflow/query/1251170/percentage-of-deleted-question-per-tag-only-existing-tags
; with tags_cte AS
(
SELECT
  Id PostId,
  replace(value,'>','') TagName
FROM
  PostsWithDeleted
  CROSS APPLY STRING_SPLIT(Tags,'<')
Where posttypeid = 1
and len(value) > 0
),
tagstats_cte as
(
SELECT
t.TagName AS TagName,
Count(q.Id) AS QuestionCount,
Count(q.DeletionDate) AS DeletedCount
FROM PostsWithDeleted q INNER JOIN tags_cte t ON q.Id = t.PostId
WHERE (q.PostTypeId = 1)
  AND (q.CreationDate >= '##Date1?2000-01-01##') AND (q.CreationDate <= '##Date2?2030-01-01##')
GROUP BY t.TagName
)

SELECT
  TagName,
  ROUND((CAST(DeletedCount AS decimal))*100.0/(CAST(QuestionCount AS decimal)),2) AS percentage,
  DeletedCount, QuestionCount
FROM tagstats_cte
WHERE QuestionCount>=##num?1000##
ORDER BY ROUND((CAST(DeletedCount AS decimal))*100.0/(CAST(QuestionCount AS decimal)),2) DESC

Enter Parameters

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