1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

--based on https://data.stackexchange.com/mathoverflow/query/1162648/percentage-of-deleted-question-per-tag
--the tags where DeletedCount=QuestionCount are omitted
--usually we're not interested in tags which have been removed and only exists on deleted questions

; 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)
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##) AND (DeletedCount<QuestionCount)
ORDER BY ROUND((CAST(DeletedCount AS decimal))*100.0/(CAST(QuestionCount AS decimal)),2) DESC

fork query

edited jun 13

created jun 13 Martin20528