0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for ebook publishers and readers

-- Enter Query Title
-- Enter Query Description

set nocount on

declare @latestDate datetime
select @latestDate = max(CreationDate) from Posts

declare @VoteStats table (PostId int, up int, down int, CreationDate datetime)

insert @VoteStats
select
    p.Id,
    up = sum(case when VoteTypeId = 2 then 1 else 0 end),
    down = sum(case when VoteTypeId = 3 then 1 else 0 end),
    p.CreationDate

from Votes v join posts p on v.postid = p.id
where v.VoteTypeId in (2,3)
and p.CommunityOwnedDate is null
and datediff(day, p.CreationDate, v.CreationDate) > 60
group by p.Id, p.CreationDate

set nocount off

--select top 200 p.id as [Post Link],
--(up * 10 - down * 2)/datediff(day, p.CreationDate, latestDate) as [Votes Per Day]
--from votes v
--join posts p on v.postid = p.id
--join @VoteStats vs on v.PostId = vs.PostId
--where datediff(day, p.creationdate, v.creationdate) > 30
--group by p.id

select top 2000 PostId as [Post Link],
(up * 10 - down * 2)/datediff(day, CreationDate, @latestDate) as average
from @VoteStats
order by average desc

fork query

created jul 17 10 anonymous