Median time for spam post deletion

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

;  with spam_cte as
(SELECT DISTINCT 
  p.Id AS pid,
  DATEDIFF(minute, p.CreationDate, p.DeletionDate) AS Minutes
FROM PostsWithDeleted p INNER JOIN Votes v ON v.PostId=p.Id
WHERE v.VoteTypeId=##votetype?12## 
AND (p.DeletionDate IS NOT NULL))

SELECT
 (
  (SELECT MAX(Minutes) FROM (SELECT TOP 50 PERCENT Minutes FROM spam_cte ORDER BY Minutes) AS BottomHalf) 
  +
  (SELECT MIN(Minutes) FROM (SELECT TOP 50 PERCENT Minutes FROM spam_cte ORDER BY Minutes DESC) AS TopHalf) 
  ) / 2  
  AS [Median]

Enter Parameters

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