Average number of days until deletion - per tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers

Q&A for Drupal developers and administrators

; with deletions_cte AS
(
SELECT
  Id PostId,
  DATEDIFF(day, CreationDate, DeletionDate) AS Days,
  replace(value,'>','') TagName
FROM
  PostsWithDeleted
  CROSS APPLY STRING_SPLIT(Tags,'<')
---we cannot use the PostTags table - since it does not contain the data about deleted posts
  WHERE (posttypeid = 1) AND (len(value) > 0)
    AND (DeletionDate IS NOT NULL)
    AND (CreationDate >= '##Date1?2010-01-01##') AND (CreationDate <= '##Date2?2030-01-01##')
)

SELECT
  d.TagName,
  AVG(CAST(d.Days AS decimal)) AS [Days until deletion]
FROM deletions_cte d INNER JOIN Tags t ON d.TagName=t.Tagname
WHERE (t.Count>=##min?1000##)
GROUP BY d.TagName
ORDER BY AVG(CAST(d.Days AS decimal)) ASC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results