select distinct q.Id as [Post Link], q.CreationDate as Cr...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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',
case
  when hnq.CreationDate is null then ''
  else (
    case 
      when (hnq.CreationDate > aa.CreationDate)
      then 'Created Before' 
      else (
        case 
          when ((lhnq.CreationDate < aa.CreationDate) or (DateAdd(day, 1, hnq.CreationDate) < aa.CreationDate))
          then 'Created After'
          else 'Created During'
          end)
      end)
  end as 'HNQ Effect'
from 
Posts as q 
left join Posts as aa on q.Id = aa.ParentId and aa.Id = q.AcceptedAnswerId and (aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null)
left join Posts as hs on q.Id = hs.ParentId and aa.Id != hs.Id and aa.Score < hs.Score
left join PostLinks as pl on q.Id = pl.PostId
left join PostHistory as hnq on q.Id = hnq.PostId and hnq.PostHistoryTypeId=52
left join PostHistory as lhnq on q.id = lhnq.PostId and lhnq.PostHistoryTypeId=53
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;

Enter Parameters

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