Threads affected by the unpinning of the accepted answer (as described in https://meta.stackexchange.com/questions/369568/unpinning-the-accepted-answer-from-the-top-of-the-list-of-answers), i.e., threads with answers that score higher than the accepted answer, where the accepted answer was not written by the OP.
Q&A for science fiction and fantasy enthusiasts
q.Id as [Post Link],
q.CreationDate as Created,
when q.ClosedDate is null then ''
else (case when pl.LinkTypeId = 3 then 'dupe' else 'closed' end)
end as 'Status',
aa.Score as 'Accepted score',
hs.Score as 'Highest score',
hs.Score - aa.Score as 'Score difference'
Posts as q
left join Posts as aa on q.Id = aa.ParentId
left join Posts as hs on q.Id = hs.ParentId
left join PostLinks as pl on q.Id = pl.PostId
q.PostTypeId = 1 and
q.AcceptedAnswerId is not null and
q.AnswerCount > 1 and
aa.Score < hs.Score and
aa.Id = q.AcceptedAnswerId and
hs.Score = (Select max(score) from Posts csq where csq.ParentId = q.id) and
(aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null) and
((pl.LinkTypeId = (Select max(LinkTypeId) from PostLinks cpl where cpl.PostId = q.id))or pl.LinkTypeId is null)
order by hs.Score - aa.Score DESC;