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