6

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for biology researchers, academics, and students

WITH rawData(uid,userrep,uncapped,capped,accepts,bounties)
AS(
SELECT
    tmp.[User Link],
    tmp.[Reputation],
    SUM(tmp.voteTotal) AS [Uncapped Votes],
    SUM(CASE
        WHEN tmp.voteTotal >= 200 THEN 200
        ELSE tmp.voteTotal END
    ) AS [Capped Votes],
    SUM(tmp.acceptTotal) AS [Accepts],
    SUM(tmp.bountyTotal) AS [Bounties]
--    CAST(100.0 * SUM(tmp.acceptTotal) / tmp.[Reputation] AS DECIMAL(20, 3)) AS [% from Accepts]
FROM
    (SELECT
        v.CreationDate,
        COUNT(v.Id) AS votecount,
        p.OwnerUserId AS [User Link],
        u.Reputation AS [Reputation],
        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 voteTotal,
        SUM((CASE
            WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki
            WHEN v.VoteTypeId = 1 THEN 15                     -- Accepted answer
            ELSE 0
        END)) AS acceptTotal,
        SUM((CASE
            WHEN v.VoteTypeId = 9 THEN BountyAmount           -- Earned Bounty
            ELSE 0
        END)) AS bountyTotal
    FROM Votes v
        JOIN VoteTypes vt ON vt.Id = v.VoteTypeId
        JOIN Posts p ON p.Id = v.PostId
        JOIN Users u ON u.Id = p.OwnerUserId
    WHERE
        p.OwnerUserId is not null and
        u.Reputation = 1
    GROUP BY
        u.Reputation,
        v.CreationDate,
        p.OwnerUserId
    ) AS tmp
    
GROUP BY
    tmp.[User Link],
    tmp.[Reputation]
)

SELECT TOP 1000
    uid AS [User Link],
    userrep AS [Reputation],
    capped + accepts + bounties AS [DE Recalced Rep],
    uncapped AS [Uncapped Votes],
    capped AS [Capped Votes],
    accepts AS [Accepts],
    bounties AS [Bounties]
FROM rawData
ORDER BY [DE Recalced Rep] DESC

fork query

edited may 2 13

created may 2 13 anonymous