0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @UID int = ##UserId##

--quesitons
select DATEADD (month, DATEDIFF (month, 0, q.mad), 0),
       RepVote = ((q.RepGain+q.RepLoss)/q.views)*q.TotVotes
from(
  select mad = pv.made,
         TotVotes = Cast(pv.UpVotes+pv.DownVotes as float),
         views = cast(pv.viewed as float),
         RepGain = case when pv.UpVotes!=0 then (pv.UpVotes*10) else 0 end,
         RepLoss = case when pv.DownVotes!=0 then (pv.DownVotes*(-2)) else 0 end
  from (
    select  made = p.CreationDate,
            viewed = p.ViewCount,
            UpVotes = sum( case when VoteTypeId=2 then 1 else 0 end ),
            DownVotes = sum( case when VoteTypeId=3 then 1 else 0 end )
    from Posts p
    join Votes v on v.Postid = p.id
    where OwnerUserId = @UID and p.PostTypeId=1
    group by p.CreationDate,p.ViewCount
  ) pv
) q
ORDER BY DATEADD (month, DATEDIFF (month, 0, q.mad), 0) ASC

fork query

edited sep 13 19

created sep 12 19 anonymous