0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

with controversial as  
(
select
PostId, sum(case when Id is null then 0 else 1 end) as downvotes
    from Votes where VoteTypeId  = 3 group by PostId
)

select 
distinct q.Id as [Post Link], 
aac.downvotes as 'aa dv',
hsc.downvotes as 'hs dv',
aa.Score as 'accepted answer score', 
hs.Score as 'highest answer score'

from 
Posts as q 
left join Posts as aa on q.Id = aa.ParentId --- aa: accepted answer
left join Posts as hs on q.Id = hs.ParentId --- hs: highest scoring answer
left join controversial as aac on aa.Id = aac.PostId --- aac: accepted answer controversy
left join controversial as hsc on hs.Id = hsc.PostId --- hsc: higest scoring answer controversy
where 
q.PostTypeId = 1 and
q.AcceptedAnswerId is not null and
q.AnswerCount > 1 and
aa.Id <> hs.Id and
aa.Score <= hs.Score and
aa.Id = q.AcceptedAnswerId and
hs.Score = (Select max(score) from Posts csq where csq.ParentId = q.id);
---order by 'score difference' DESC;

view current version

edited sep 20 21

created sep 8 21 user2324435523