The Roomba 365 day rule---what happens if we drop the 'has 1 or 0 comments‘ requirement

2

Please login or register to vote for this query.

(click on this box to dismiss)

TeX - LaTeX

Q&A for users of TeX, LaTeX, ConTeXt, and related typesetting systems

-- If the question is more than 365 days old, and ...
select TOP 10000 Posts.CreationDate, Posts.OwnerUserId, Users.DisplayName, ViewCount, 
  Posts.Id as [Post Link], Tags
from Posts 
     ,Users

where 
  -- joining tables
  Users.Id = Posts.OwnerUserId 
  
  -- satisfying conditions
  and Posts.PostTypeId = 1 -- is a question
  and Posts.Score <= 0 -- has a score of 0
  and Posts.AnswerCount = 0 -- has no answers
  and Posts.CommentCount >= 2  -- has 2 or more comments
  and Posts.ViewCount <= DATEDIFF(DAY, Posts.CreationDate, GETDATE()) * 1.5 -- has view count <= the age of the question in days times 1.5
  and DATEDIFF(DAY, Posts.CreationDate, GETDATE()) > 365 -- is old
  
  -- post is not locked
  and not exists (
        select PostHistory.Id from PostHistory 
        where PostHistory.PostId = Posts.Id
        and PostHistory.PostHistoryTypeId = 14 -- post is locked
  )
order by Posts.CreationDate asc


/*
Schema documentation: http://meta.stackexchange.com/a/2678/178179   
 */

Enter Parameters

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