1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jun 15 16

created jun 15 16 amaranth