5

Please login or register to vote for this query.

(click on this box to dismiss)

Cliff questions are questions on the cliff of being deleted by the automatic cleanup. See http://meta.stackoverflow.com/questions/78048/enable-automatic-deletion-of-old-unanswered-zero-score-questions-after-a-year/92006#92006 for more information on this cleanup. Often, it will take just one or two downvotes to push it over the cliff into deletion. Frankly, there's a lot of crap just sitting on the edge that isn't helping anyone, but got an upvote somewhere along the way.

Q&A for information security professionals

with T as (
select
  q.id as [Post Link],
  q.closedDate,
  q.score as "qScore",
  answers.scores as "aScores",
  case ph.Comment
    when 1 then 'Dup'
    when 2 then 'Off Topic'
    when 3 then 'Subjective'
    when 4 then 'NARQ'
    when 7 then 'TL'
    else ph.Comment
  end as "Close Reason",
  q.viewCount as "Views",
  answers.blen as "Answer length"
from
  Posts q
  join (
    select
      a.parentId as pid,
      sum(a.score) as scores,
      sum(len(a.body)) as blen
    from
      Posts a
    where
      a.PostTypeId = 2
      and a.score > 0
    group by
      a.parentId
    having
      sum(a.score) <= ##PositiveAnswerScore:int?2##
      and sum(len(a.body)) <= ##AnswerLen:int?1000##
  ) answers on (q.id = answers.pid)
  join PostHistory ph on (ph.postid = q.id)
where
  q.closedDate is not null
  and q.closedDate >=
    CONVERT (datetime, ##StartDate:string?2014.01.01## , 102)
  and q.closedDate <=
    CONVERT (datetime, ##EndDate:string?2014.01.31## , 102)
  and q.PostTypeId = 1
  and not exists (
    select 1
    from
      PostHistory ph
    where
      ph.PostId = q.id
      and ph.PostHistoryTypeId = 14
  )
  and ph.PostHistoryTypeId = 10
  and q.score <= ##QScore:int?2##
  and q.score + answers.scores <= ##SumScore:int?2##
  and ph.Comment not in (1, 101) --- No dups
  and q.AcceptedAnswerId is null
  )
  
  Select TOP(##RowsReturned:int?100##) * from T
  order by T.qScore + T.aScores

fork query

edited mar 1 14

created aug 27 13 MichaelT