0

Please login or register to vote for this query.

(click on this box to dismiss)

Há algumas falhas nela mas dá uma ideia básica.

Perguntas e respostas para programadores profissionais e entusiastas

-- % of current reputation lost to repcap among the top 1000 users.

-- History:
--     May 6, 2013: Optimized the query.


-- Get Table of top 1000 users
WITH userData(uid,rep,pcnt)
AS(
SElECT TOP 1000
    Users.Id,
    Reputation,
    SUM(CASE
        WHEN PostTypeId = 1 THEN 1
        WHEN PostTypeId = 2 THEN 1
    END)
FROM
    Posts
  INNER JOIN
    Users ON Users.Id = Posts.OwnerUserId
GROUP BY
    Users.Id,
    Users.Reputation
ORDER BY Reputation DESC
),

-- Compute uncapped rep of all users
repData(uid,uncapped)
AS(
SELECT
    Users.Id,
--    COUNT(*),
    SUM(CASE
        WHEN VoteTypeId = 9 THEN BountyAmount                     -- Earned Bounty
        WHEN Votes.CreationDate > Posts.CommunityOwnedDate THEN 0 -- After Community Wiki
        WHEN VoteTypeId = 1 THEN 15                               -- Accepted answer
        WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 10            -- Upvoted question
        WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10            -- Upvoted answer
        WHEN VoteTypeId = 3 THEN -2                               -- Downvote
        ELSE 0 END
    )
FROM
    Votes
  INNER JOIN 
    Posts ON Posts.Id = Votes.PostId
  INNER JOIN
    Users ON Users.Id = Posts.OwnerUserId
WHERE Posts.PostTypeId = 1 or PostTypeId = 2
GROUP BY
    Users.Id,
    Users.Reputation
)

-- Join tables
SELECT
    u.uid AS [User Link],
    u.pcnt AS [Posts],
--    r.votes AS [Votes Received],
    u.rep AS [Rep],
    r.uncapped AS [Uncapped Rep],
    r.uncapped - u.rep AS [Rep Lost],
    Cast(100. * (r.uncapped - u.rep) / u.rep AS DECIMAL(20, 3)) AS [Percent Lost],
    Cast(1.0 * u.rep      / u.pcnt AS DECIMAL(20, 3)) AS [Rep / Post],
    Cast(1.0 * r.uncapped / u.pcnt AS DECIMAL(20, 3)) AS [Uncapped Rep / Post]
FROM userData u
    LEFT OUTER JOIN repData r ON u.uid = r.uid
ORDER BY
    [Rep Lost] DESC

fork query

edited feb 11 20 maniero

created nov 12 12 Mysticial