Q&A for professional and enthusiast programmers
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',
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',
when hnq.CreationDate is null then ''
when (hnq.CreationDate > aa.CreationDate)
then 'Created Before'
when ((lhnq.CreationDate < aa.CreationDate) or (DateAdd(day, 1, hnq.CreationDate) < aa.CreationDate))
then 'Created After'
else 'Created During'
end as 'HNQ Effect'
Posts as q
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
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
q.PostTypeId = 1 and
q.AnswerCount > 1 and
(aa.OwnerUserId != q.OwnerUserId or aa.OwnerUserId is null)
) 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
(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)