1

(click on this box to dismiss)

View vote counts on self-answered questions.

``````-- AnswerDelay: Max time between Q and A in min
-- VoteDelay: Votes in first N days
declare @vote_delay int = ##VoteDelay:int?7##

select q.Id,
q.CreationDate,
sum(case when v.VoteTypeId = 2 and v.PostId = q.Id then 1 else 0 end)
as [Q↑],
sum(case when v.VoteTypeId = 3 and v.PostId = q.Id then 1 else 0 end)
as [Q↓],
sum(case when v.VoteTypeId = 2 and v.PostId = a.Id then 1 else 0 end)
as [A↑],
sum(case when v.VoteTypeId = 3 and v.PostId = a.Id then 1 else 0 end)
as [A↓]
into #tmp
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) <= @answer_delay
inner join Votes v on v.PostId in (q.Id, a.Id)
and v.VoteTypeId in (2, 3)
and datediff(day, q.CreationDate, v.CreationDate) <= @vote_delay
group by q.Id, q.CreationDate--, a.Id

select Id as [Post Link], CreationDate,
[Q↑]-[Q↓] as [Q], [Q↑], [Q↓],
[A↑]-[A↓] as [A], [A↑], [A↓],
[Q↑]+[A↑]-[Q↓]-[A↓] as [Tot],
[Q↑]+[A↑] as [Tot↑],
[Q↓]+[A↓] as [Tot↓]
into #table
from #tmp

select *
from #table
order by Tot desc

select row_number() over (order by Tot desc) as Rank,
[Q↑], -[Q↓] as [Q↓], [A↑], -[A↓] as [A↓]
--    case when [Q↑]+[Q↓] > 0 then
--        100.0*[Q↑]/([Q↑]+[Q↓]) else null end as [Q↑ %],
--    case when [A↑]+[A↓] > 0 then
--        100.0*[A↑]/([A↑]+[A↓]) else null end as [A↑ %]
from #table``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached