3

Please login or register to vote for this query.

(click on this box to dismiss)

The roomba for these questions runs on Saturdays. Depending on the post score and whether the poster's account has been deleted, these questions could be eligible for the roomba after either 30 or 365 days. See http://stackoverflow.com/help/roomba

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- If the question is more than 365 days old, and ...
select top (##TopN:int?200##)
  Posts.Id as [Post Link],
  Score,
  ViewCount, 
  Posts.CreationDate,
    (case when Users.Id is not null then
      'site://users/' + cast(Users.Id as nvarchar) + '|' else '' end)
      + coalesce(Users.DisplayName, Posts.OwnerDisplayName) as [User Link],
  Users.LastAccessDate
from Posts
left join Users on Users.Id = Posts.OwnerUserId 
where Posts.PostTypeId = 1 -- is a question
  and (Posts.Score <= ##MaxPostScore:int?1##
    or Posts.Score <= ##MaxPostScore##+1 and Posts.OwnerUserId is null)
  and Posts.AnswerCount = 0
  and
    (-- Posts potentially eligible for the 365-day rule:
     (Posts.CommentCount < 2
      and Posts.ViewCount <= datediff(day, Posts.CreationDate, getdate()) * 1.5) -- has view count <= the age of the question in days times 1.5
     -- Posts potentially eligible for the 30-day rule:
     or (Posts.Score <= ##MaxPostScore##-1
       or (Posts.Score <= ##MaxPostScore## and Posts.OwnerUserId is null))
    )
  and datediff(day, cast(##CutoffDate:string?2015-01-01## as datetime),
               Posts.CreationDate) < 0 -- is old
  and Posts.ClosedDate is null
  -- 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 case when Users.Id is null then 0 else 1 end,
    Posts.CreationDate asc

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

view current version

edited jul 1 16 amaranth

created mar 29 15 Franck Dernoncourt