View vote counts on self-answered questions.
Q&A for amateur radio enthusiasts
-- AnswerDelay: Max time between Q and A in min declare @answer_delay int = ##AnswerDelay:int?5## -- 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