0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for ebook publishers and readers

declare @minrep as int = ##minrep:int?10000##;

with TopUsers as (
    select Id as TopUser, DisplayName
    from Users
    where Reputation >= @minrep
), RepDays as (
            --Approved suggested edits.
            select TopUser, 
                   Convert(Date, se.ApprovalDate) as tstamp,
                   sum(2) as rep
            from TopUsers inner join SuggestedEdits se on TopUser = se.OwnerUserId
              and se.ApprovalDate is not null
            group by TopUser, Convert(Date, se.ApprovalDate)
              
         UNION ALL
      
            -- Up and Down votes on Q's and A's
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(
                   case when v.VoteTypeId = 1 then 15
                        when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer
                        when v.VoteTypeId = 3 then -2
                        when v.VoteTypeId = 9 then IsNull(v.BountyAmount, 0)
                        else 0
                   end) as rep
            from TopUsers
            inner join Posts p on TopUser = p.OwnerUserId
              and p.PostTypeId in (1,2)
            inner join Votes v on p.Id = v.PostId
              and v.VoteTypeId in (1,2,3,9)
            group by TopUser, Convert(Date, v.CreationDate)
              
         UNION ALL
         
            -- Bounties that were offered....
            select TopUser,
                   Convert(Date, v.CreationDate) as tstamp,
                   sum(-1 * v.BountyAmount) as rep
            from TopUsers inner join Votes v on TopUser = v.UserId
              and v.VoteTypeId = 8
            group by TopUser, Convert(Date, v.CreationDate)
), RepCumulative as (

    select TopUser,
           tstamp,
           rep,
           sum (rep) over (partition by TopUser order by tstamp) as CumRep
    from RepDays

)
select *
from RepCumulative
where CumRep between @minrep and (@minrep + 500)
order by TopUser, tstamp

view current version

edited dec 10 14

created jan 17 14 rolfl