declare @now as DateTime = (select max(CreationDate) from...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Overflow

Q&A about the site for professional and enthusiast programmers

declare @now as DateTime = (select max(CreationDate) from Votes);

/*
  Look at all votes (including anon votes) cast over a window of time
  Attach to *questions* - so, all votes for answers count as well.
*/
with RelVotes as
(
select PostId, q.Id QuestionId, v.CreationDate
from Votes v
join Posts p on p.Id=v.PostId
join Posts q on q.Id=isnull(p.ParentId, p.Id)

where VoteTypeId in (2,3)
and v.CreationDate > @now-##LookbackDays?30##

union all

select PostId, q.Id QuestionId, v.CreationDate
from PostFeedback v
join Posts p on p.Id=v.PostId
join Posts q on q.Id=isnull(p.ParentId, p.Id)

where VoteTypeId in (2,3)
and v.CreationDate > @now-##LookbackDays##
)

/*
 calculate an average "velocity" for each day in a smaller, recent window of time
 along with an average velocity for each day in the remainder of the overall window
 "velocity" is defined as average # of votes per day
*/
select QuestionId, 
1.0*sum(iif(CreationDate<@now-##FocalDays?7##, 1, 0))/(##LookbackDays##-##FocalDays##) previousAvgVelocity,
1.0*sum(iif(CreationDate>=@now-##FocalDays##, 1, 0))/(##FocalDays##) currentAvgVelocity
into #QuestionAvgVelocities
from RelVotes
group by QuestionId
;

/*
 Defining acceleration as the difference between current and previous avg velocity.
 Yeah, these definitions are ... a little dodgy. BUT,
 the primary goal here is to come up with a number for each question that can 
 be compared in a meaningful fashion, even across sites 
 (although this query DOESN'T do that, its origin was a script that would calculate 
 this data for every meta site and rank across them)
*/
with Acceleration as
(
select y.QuestionId, currentAvgVelocity-previousAvgVelocity acceleration
from #QuestionAvgVelocities y
)

select top 100 QuestionId [Post Link], 
  datediff(day, p.CreationDate, @now) "days old", 
  acceleration
from Acceleration acc
join Posts p on p.Id=acc.QuestionId and p.DeletionDate is null
where acceleration > 0
order by acceleration desc

Enter Parameters

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