2

Please login or register to vote for this query.

(click on this box to dismiss)

These questions would be automatically deleted by the RemoveAbandonedQuestions job, if they didn't have at least two comments. Some of these comments were written by you (or by the user whose user ID you've entered below, if not your own). If the feature request at http://meta.stackoverflow.com/q/333477 passes, these questions will be deleted. Would you like to convert any of your comments into answers before that happens?

Q&A for professional and enthusiast programmers

-- UserId: User ID "Enter your (or someone else's) numerical user ID on the site here. You can find this in your user profile page URL."
-- MinAge: Minimum Q age (days) "Only list questions at least this many days old. The roomba only deletes questions older than 365 days."
-- IgnoreViews: Ignore view count (0/1) "If set to 1, list questions even if they're currently ineligible for deletion due to getting more than 1.5 views per day."

with FoundComments as (select
  Posts.Id as PostId,
  Posts.Score as PostScore,
  Posts.ViewCount as PostViews,
  Posts.CreationDate as PostDate,
  Posts.CommentCount as PostComments,
  Sum(Comments.Score) over (partition by Posts.Id) as CommentScore,
  Count(*) over (partition by Posts.Id) as UserComments,
  Comments.Text as CommentText,
  row_number() over (partition by Posts.Id order by Comments.CreationDate asc) as RowNumber
from Comments
  join Posts on Posts.Id = Comments.PostId
where Comments.UserId = ##UserId:int##
  -- select the post if it is a question:
  and Posts.PostTypeId = 1
  -- ...that was posted at least MinAge days ago:
  and Posts.CreationDate <= DateAdd(Day, -##MinAge:int?365##, GetUTCDate())
  -- ...and has a score of 0, or 1 if the owner's account is deleted:
  and Posts.Score between 0 and 1
  and (Posts.Score = 0 or Posts.OwnerUserId is null)
  -- ...and has no answers:
  and Posts.AnswerCount = 0
  -- ...and is not locked:
  and Coalesce((
    select top 1 PostHistoryTypeId from PostHistory
    where PostHistory.PostId = Posts.Id
      and PostHistoryTypeId in (14, 15)  -- lock, unlock
    order by PostHistory.Id desc
  ), 0) <> 14
  -- and has been viewed no more than 1.5 times a day on average:
  and (##IgnoreViews:int?0## > 0 or Posts.ViewCount <= 1.5 * DateDiff(Day, Posts.CreationDate, GetUTCDate()))
  -- and has more than one comment (else it would already be eligible for deletion):
  and Posts.CommentCount > 1
)
select
  PostId as [Post Link],
  PostScore as [Score],
  PostViews as [Views],
  Convert(Char(10), PostDate, 127) as [Asked],
  PostComments as [Comments],
  UserComments as [By User],
  CommentScore as [Upvotes],
  -- http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
  -- XXX: this is still way too slow :-(
  -- Stuff((
  --   select ' | ' + Cast(C.CommentText as VarChar(Max))
  --   from FoundComments as C where C.PostId = F.PostId
  --   for XML Path(''), Type
  -- ).value('(./text())[1]', 'VarChar(Max)'), 1, 3, '') as [Comment Text]
  CommentText as [First Comment Text]
from FoundComments as F
where RowNumber = 1
order by PostId

fork query

edited sep 1 16

created sep 1 16 Ilmari Karonen