How many question would be deleted by roomba if it ignored tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT t.TagName, 
       u.Roombad, COUNT(*) as TotalQuestionsInTag,
       u.Roombad * 100.0 / COUNT(*) as PercentageOfRoombad
FROM (
  SELECT t.Id as TagId, COUNT(p.Id) as Roombad
  FROM Tags t
  INNER JOIN PostTags pt ON pt.TagId = t.id
  INNER JOIN Posts p ON p.Id = pt.PostId
  WHERE 
      p.ClosedDate is null and
      (-- 365-day rule (RemoveAbandondedQuestions)
     ((p.Score <= 0 or (p.Score <= 1 and p.OwnerUserId is null))
      -- has view count <= the age of the question in days times 1.5
      and p.ViewCount <= datediff(day, p.CreationDate, getdate())*1.5
      -- has too many comments
      and p.CommentCount >= 2
     )
    )
    -- has no answers
    and p.AnswerCount = 0
    -- post is not locked
    and coalesce((
      SELECT TOP 1 ph.PostHistoryTypeId
      FROM PostHistory ph
      WHERE ph.PostId = p.Id
        AND ph.PostHistoryTypeId in (14, 15) -- post locked, unlocked
      ORDER BY ph.Id desc
    ), 0) <> 14
    -- post is old
    and datediff(day, p.CreationDate, getdate()) > 365
    -- not deleted
    and p.DeletionDate is null
  GROUP BY t.Id
) u
INNER JOIN PostTags pt ON pt.TagId = u.TagId
INNER JOIN Posts p ON p.Id = pt.PostId
INNER JOIN Tags t ON t.Id = u.TagId
GROUP BY t.TagName, u.Roombad
ORDER BY 4 DESC;

Enter Parameters

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