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

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

select top (##TopN:int?300##)
  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.ClosedDate is null and
  (-- 365-day rule (RemoveAbandondedQuestions)
   ((Posts.Score <= ##MaxPostScore:int?1##
     or (Posts.Score <= ##MaxPostScore##+1 and Posts.OwnerUserId is null)
    )
    -- has view count <= the age of the question in days times 1.5
    and Posts.ViewCount <= datediff(day, Posts.CreationDate, getdate())*1.5
    -- has 1 or 0 comments
    and Posts.CommentCount < 2
   )
   -- 30-day rule (RemoveDeadQuestions)
   or (Posts.Score <= ##MaxPostScore##-1)
  )
  -- has no answers
  and Posts.AnswerCount = 0
  -- post is not locked
  and coalesce((select top 1 PostHistoryTypeId
    from PostHistory
    where PostHistory.PostId = Posts.Id
      and PostHistory.PostHistoryTypeId in (14, 15) -- post locked, unlocked
    order by Id desc), 0) <> 14
  -- post is old
  and datediff(day, cast(##CutoffDate:string?2015-01-01## as datetime),
               Posts.CreationDate) < 0
  and Posts.Tags like '%' + ##Tags:string?$## + '%'
order by case when Users.Id is null then 0 else 1 end,
    Posts.CreationDate asc

view current version

edited aug 4 16 amaranth

created mar 29 15 Franck Dernoncourt