0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

select distinct 
q.Id as [Post Link], 
q.CreationDate as Created,
case 
  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'
from 
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
where 
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;
order by 'Score difference' DESC;

fork query

edited oct 5

created sep 17 anonymous