Vote count extremes by date

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

set nocount on

declare @UserId int = ##UserId##;

declare @UpvoteStats table (Day DateTime, VoteCount int)
declare @AcceptStats table (Day DateTime, VoteCount int)
declare @DownvoteStats table (Day DateTime, VoteCount int)

insert @UpvoteStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  count(votes.Id) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 2
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
insert @AcceptStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  Convert(int,count(votes.Id)) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 1
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
insert @DownvoteStats
select
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0) as Day,
  count(votes.Id) as VoteCount
from votes, posts
where
  votes.VoteTypeId = 3
  and posts.OwnerUserId = @UserId
  and votes.PostId = posts.Id
group by
  DATEADD(dd,(DATEDIFF(dd,0,votes.CreationDate)),0)
order by
  VoteCount desc
  
set nocount off
  
select top 20
  uv.Day as Day,
  uv.VoteCount * 10 as Upvotes,
  av.VoteCount * 15 as Accepts,
  dv.VoteCount * 2 as Downvotes
from @UpvoteStats uv, @DownvoteStats dv, @AcceptStats av
where
  av.Day = uv.Day
  and dv.Day = uv.Day
order by
  Accepts desc, Upvotes desc, Downvotes asc;

Enter Parameters

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