Questions with 2+ answers, the highest scoring answer is accepted and tied with another answer

0

Please login or register to vote for this query.

(click on this box to dismiss)

Coffee

Q&A for people interested in all aspects of producing and consuming coffee

select 
distinct q.Id as [Post Link], 
aa.Score as 'accepted answer score',
shs.Score as 'second highest answer score',
aa.Score - shs.Score as 'score difference'
from 
Posts as q 
left join Posts as aa on q.Id = aa.ParentId --- aa: accepted answer
left join Posts as shs on q.Id = shs.ParentId --- shs: second highest scoring answer
where 
q.PostTypeId = 1 and
q.AcceptedAnswerId is not null and
q.AnswerCount > 1 and
aa.Id = q.AcceptedAnswerId and
aa.Score = (Select max(score) from Posts csq where csq.ParentId = q.id) and
shs.Score = (Select max(score) from Posts csq where 
              csq.ParentId = q.id and csq.Id != q.AcceptedAnswerId) and
aa.Score - shs.Score = 0
order by 'score difference' ASC, aa.Score DESC;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results