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',
  tbl.AAS as 'Accepted score', 
  tbl.HS as 'Highest score',
  tbl.HSAC as 'Count of Higher Scoring Answers',
  tbl.AHS as 'Average Higher Scores',
  tbl.SD 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
  Join (
    Select 
      avg(q.Id) as qId,
      avg(aa.Id) as aaId,
      count(hs.Id) as HSAC, 
      avg(aa.Score) as AAS,
      max(hs.Score) as HS,
      avg(hs.Score) as AHS,
      max(hs.Score) - avg(aa.Score) as SD
    From 
      Posts as q
      join Posts as aa on q.Id = aa.ParentId and aa.Id = q.AcceptedAnswerId
      join Posts as hs on q.Id = hs.ParentId and aa.Id != hs.Id and aa.Score < hs.Score
    Where
      q.PostTypeId = 1 and
      q.AnswerCount > 1 and
      (aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null)
    Group by 
      q.Id
  ) as tbl on q.Id = tbl.qId
  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
  left join Posts as aa on aa.Id = tbl.aaId
  left join PostLinks as pl on q.Id = pl.PostId
Where
(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)

Enter Parameters

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