0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who base their lives on Jewish law and tradition and anyone interested in learning more

-- Finds question posts that are open, unanswered, at least six months old,
-- and with comments saying the word "answer". Counts up such comments as 
-- well as comments that look even more like they're asking for an answer.
-- Goal: Find questions that have answers hiding in the comments.

select
  pq.id as [Post Link],
  pq.score,
  pq.creationdate,
  count(c.id) as CommentsMentioningAnswer,
  sum(case when
       lower(c.text) like '%post an answer%' 
    or lower(c.text) like '%submit an answer%'
    or lower(c.text) like '%please answer%'
    or lower(c.text) like '%answer already%'
    or lower(c.text) like '%your answer%'
    or lower(c.text) like '%like an answer%'
    or lower(c.text) like '%as an answer%'
    then 1 else 0 end) as CommentsRequestingAnswer

from comments c
inner join posts pq
  on pq.id = c.postid
  and pq.posttypeid = 1 -- question
  and pq.closeddate is null -- open
  and datediff(d, pq.creationdate, getdate()) > 182 -- six months old
left outer join posts pa
  on pa.parentid = pq.id
  and pa.posttypeid = 2 -- answer
  and pa.score > 0 -- valuable
where lower(c.text) like '%answer%'
group by pq.id, pq.score, pq.creationdate
having count(pa.id) = 0
order by pq.score desc, pq.creationdate asc

fork query

edited aug 11 16

created aug 11 16 Isaac Moses