1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and end users of the Sitecore CMS and multichannel marketing software

with RepChanges as (
    select p.OwnerUserId,
        v.CreationDate,
        SUM(case
            when v.VoteTypeId = 9 then v.BountyAmount
            when v.CreationDate > p.CommunityOwnedDate then 0
            when v.VoteTypeId = 1 then 15
            else 0
        end) as Uncapped,
        SUM(case
            when v.CreationDate > p.CommunityOwnedDate then 0
            when v.VoteTypeId = 2 and p.PostTypeId = 1 then 5
            when v.VoteTypeId = 2 and p.PostTypeId = 2 then 10
            when v.VoteTypeId = 3 then -2
            else 0
        end) as Capped,
        SUM(case
            when v.VoteTypeId = 9 then v.BountyAmount
            when v.CreationDate > p.CommunityOwnedDate then 0
            when v.VoteTypeId = 1 then 15
            else 0
        end) as Uncapped2,
        SUM(case
            when v.CreationDate > p.CommunityOwnedDate then 0
            when v.VoteTypeId = 2 then 10
            when v.VoteTypeId = 3 then -2
            else 0
        end) as Capped2
    from Votes v
    left join Posts p
    on v.PostId = p.Id
    group by p.OwnerUserId,
        v.CreationDate
),

UserRep as (
    select u.Id,
        SUM(r.Uncapped) + SUM(case
            when r.Capped > 200 then 200
            else r.Capped
        end) as [Estimated Rep],
        SUM(r.Uncapped2) + SUM(case
            when r.Capped2 > 200 then 200
            else r.Capped2
        end) as [Estimated After Increase]
    from Users u
    inner join RepChanges r
    on u.Id = r.OwnerUserId
    group by u.Id
)

select top 100
    u.Id [User Link],
    u.Reputation as [Actual Rep],
    r.[Estimated Rep],
    r.[Estimated After Increase],
    r.[Estimated After Increase] - r.[Estimated Rep] as [Amount Increased]
from Users u
inner join UserRep r
on u.Id = r.Id
order by u.Reputation desc

view current version

edited jan 12 16 anonymous

created apr 21 13 Mysticial