0

Please login or register to vote for this query.

(click on this box to dismiss)

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

--- Highest rep days for user
--- Computes the days which a user got the highest rep.
--- Takes into account upvotes, downvotes and accepts. Bounties are not factored in.

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
  CONVERT(char(10),uv.Day,126) as Day,
  uv.VoteCount * 10 as UpvoteRep,
  av.VoteCount * 15 as AcceptRep,
  -(dv.VoteCount * 2) as DownvoteRep,
  (uv.VoteCount * 10) + (av.VoteCount * 15) - (dv.VoteCount * 2) as TotalRep,
  case when
    ((uv.VoteCount * 10) - (dv.VoteCount * 2)) > 200 then 200
    else ((uv.VoteCount * 10) - (dv.VoteCount * 2))
    end + (av.VoteCount * 15) as RealRep
from @UpvoteStats uv, @DownvoteStats dv, @AcceptStats av
where
  av.Day = uv.Day
  and dv.Day = uv.Day
order by
  RealRep desc, UpvoteRep desc, AcceptRep desc, DownvoteRep asc;

fork query

edited aug 30 12

created aug 30 12 anonymous