0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

create table #posts (postid int, bountydate datetime, upvotes int, downvotes int);

insert into #posts
select p.id, bv.creationdate,
  sum(case uv.votetypeid when 2 then 1 else 0 end) as upvotes,
  sum(case uv.votetypeid when 3 then 1 else 0 end) as downvotes
  from posts as p
  inner join votes as bv
    on bv.postid = p.id
  left outer join votes as uv
    on uv.postid = bv.postid
    and uv.creationdate >= bv.creationdate
    and uv.creationdate < dateadd(day, 7, bv.creationdate)
  where bv.votetypeid = 8
    and p.posttypeid = 1
    and uv.votetypeid in (2, 3)
    and bv.creationdate between '2016-01-01' and '2016-07-01'
  group by p.id, bv.creationdate
  order by upvotes, downvotes desc;
  
select top 100 postid as [Post Link], bountydate as 'Bounty date',
  upvotes as 'Number of upvotes',
  downvotes as 'Number of downvotes'
  from #posts
  order by upvotes, downvotes desc;

select avg(cast (upvotes as float)) as 'Average number of upvotes',
       avg(cast (downvotes as float)) as 'Average number of downvotes'
  from #posts

view current version

edited jul 23 16 Glorfindel

created jul 23 16 rene