Self-answered questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

View vote counts on self-answered questions.

Moderators

Q&A for community managers, administrators, and moderators

-- MaxTime: Max time difference in minutes
declare @max_time int = ##MaxTime:int?5##

select q.Id as [Post Link],
    q.Score as [Q score],
    sum(case when v.VoteTypeId = 2 and v.PostId = q.Id then 1 else 0 end)
        as [Q up],
    sum(case when v.VoteTypeId = 3 and v.PostId = q.Id then 1 else 0 end)
        as [Q down],
    a.Score as [A score],
    sum(case when v.VoteTypeId = 2 and v.PostId = a.Id then 1 else 0 end)
        as [A up],
    sum(case when v.VoteTypeId = 3 and v.PostId = a.Id then 1 else 0 end)
        as [A down],
    q.Score + a.Score as [TotScore],
    sum(case when v.VoteTypeId = 2 and v.PostId in (q.Id, a.Id) then 1 else 0 end)
        as [TotUp],
    sum(case when v.VoteTypeId = 3 and v.PostId in (q.Id, a.Id) then 1 else 0 end)
        as [TotDown]
from Posts q
inner join Posts a on a.PostTypeId = 2
    and q.OwnerUserId = a.OwnerUserId
    and a.ParentId = q.Id
    and datediff(minute, q.CreationDate, a.CreationDate) < @max_time
inner join Votes v on v.PostId in (q.Id, a.Id)
    and v.VoteTypeId in (2, 3)
group by q.Id, q.Score, a.Id, a.Score
order by q.Id

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results