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

-- This query works by doing two rep-recalc computations.
-- One recalc is done on all posts. The second recalc is done excluding events
-- from the target post.

-- The difference between these two results is then subtracted from the actual
-- reputation number of the user.

-- Equation:
--   "Rep without Post" = "Actual Reputation" - ("Recalc All" - "Recalc without Post")

-- An alternate (faster) approach is to do one recalc without the post.
-- However, "Actual Reputation" != "Recalc All".
-- This is because the Data Explorer does not provide sufficient data to truly
-- calculate a user's rep. Events such as downvotes cast, deletions, and red flag
-- penalties are not available to the public. Therefore, there will be
-- discrepancies between the actual rep and the recalced rep.

-- The work-around implemented by this query is to do two recalcs and get the
-- difference. The hope is that the descrepancies will "cancel out" so that
-- the computed difference will be fairly accurate.


DECLARE @pid int = ##PostID##

DECLARE @uid int
SELECT @uid = OwnerUserId FROM Posts p WHERE p.Id = @pid


DECLARE @recalcF int
DECLARE @recalcR int
SELECT
    -- Calculate rep.
    @recalcF =
    SUM(tmp.Funcapped) + SUM((CASE
        WHEN tmp.Fcapped > 200 THEN 200
        ELSE tmp.Fcapped
    END)),
    
    -- Calculate rep without the target post.
    @recalcR =
    SUM(tmp.Funcapped) + SUM((CASE
        WHEN tmp.Rcapped > 200 THEN 200
        ELSE tmp.Rcapped
    END))
FROM

    (SELECT
        v.CreationDate,
        
        -- Get daily break-downs. Separate uncappable events from cappable events.
        SUM((CASE
            WHEN v.VoteTypeId = 9 THEN BountyAmount           -- Earned Bounty
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN v.VoteTypeId = 1 THEN 15                     -- Accepted answer
            ELSE 0
        END)) AS Funcapped,
        SUM((CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5     -- Upvoted question
            WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10    -- Upvoted answer
            WHEN VoteTypeId = 3 THEN -2                       -- Downvote
            ELSE 0
        END)) AS Fcapped,
        
        -- Same as above but exclude events from the target post.
        SUM((CASE
            WHEN p.Id = @pid THEN 0
            WHEN v.VoteTypeId = 9 THEN BountyAmount           -- Earned Bounty
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN v.VoteTypeId = 1 THEN 15                     -- Accepted answer
            ELSE 0
        END)) AS Runcapped,
        SUM((CASE
            WHEN p.Id = @pid THEN 0
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5     -- Upvoted question
            WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10    -- Upvoted answer
            WHEN VoteTypeId = 3 THEN -2                       -- Downvote
            ELSE 0
        END)) AS Rcapped
    FROM Votes v
        LEFT JOIN Posts p ON p.Id = v.PostId
    WHERE p.OwnerUserId = @uid
    GROUP BY
        v.CreationDate
    ) AS tmp


-- Get the difference
DECLARE @repFromPost int = @recalcF - @recalcR;

SELECT
    Id AS [User Link],
    @pid AS [Post Link],
    Reputation AS [Actual Rep],
--    @repRecalc AS [DE recalced Rep],
--    @repWOPost AS [Recalc W/O Post],
    Reputation - @repFromPost AS [Rep W/O Post],
    @repFromPost AS [Rep from Post],
    Cast(100. * @repFromPost / Reputation AS DECIMAL(20, 4)) AS [% Rep from Post]
FROM Users WHERE Id = @uid

view current version

edited apr 21 13

created apr 21 13 Mysticial