0

Please login or register to vote for this query.

(click on this box to dismiss)

Ranks users by most "passive" rep they gained in the last 30 days.

Q&A for professional and enthusiast programmers

-- Ranks users by most "passive" rep they gained in the last 30 days.

-- "Passive Rep" is rep gained from posts that are older than 30 days.
-- "Active Rep"  is rep gained from posts that are younger than 30 days.

-- Legend:
--     Passive Votes / Day: Votes received on old posts in the last 30 days.
--     Active  Votes / Day: Votes received on new posts in the last 30 days.
--     Passive Rep   / Day: Rep received* on old posts in the last 30 days.
--     Active  Rep   / Day: Rep received* on new posts in the last 30 days.

--     *Does not take into account for the repcap.


-- History:
--     Written by Mysticial (http://stackoverflow.com/users/922184/mysticial)
--     Optimized by wilx    (http://stackoverflow.com/users/341065/wilx)


declare @window       numeric = 90
declare @ageThreshold numeric = 30

declare @lastDate DATETIME
SET @lastDate = (SELECT MAX(CreationDate) FROM Votes);


--declare @rawTable table (id int, pv int, av int, pr int, ar int)
--insert @rawTable
WITH rawTable(id, pv, av, pr, ar)
AS (
SELECT TOP 500
    tmp.[User Link],
    SUM(tmp.pv) AS [Passive Votes],
    SUM(tmp.av) AS [Active Votes],
    SUM(tmp.pr) AS [Passive Rep],
    SUM(tmp.ar) AS [Active Rep]
FROM

    -- This sub-query grabs a table of posts with passive/active/votes/rep columns.
    -- The outer query then collapses all of these by user ID.
    (SELECT
        Posts.OwnerUserId AS "User Link",
        SUM(CASE
            WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) < @ageThreshold THEN 0
            WHEN VoteTypeId = 2 THEN 1
            WHEN VoteTypeId = 3 THEN -1
        END) AS pv,
        SUM(CASE
            WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) >= @ageThreshold THEN 0
            WHEN VoteTypeId = 2 THEN 1
            WHEN VoteTypeId = 3 THEN -1
        END) AS av,
        SUM(CASE
            WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) < @ageThreshold THEN 0
            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 5             -- Upvoted question
            WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10            -- Upvoted answer
            WHEN VoteTypeId = 3 THEN -2                               -- Downvote
        END) AS pr,
        SUM(CASE
            WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) >= @ageThreshold THEN 0
            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 5             -- Upvoted question
            WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10            -- Upvoted answer
            WHEN VoteTypeId = 3 THEN -2                               -- Downvote
        END) AS ar
    FROM
        Votes
      INNER JOIN
        Posts ON Posts.Id = Votes.PostId
    WHERE
        Posts.OwnerUserId is not null and
        DATEDIFF(DAY,Votes.CreationDate,@lastDate) <= @window
    GROUP BY
        Posts.OwnerUserID
    ) AS tmp

GROUP BY
    tmp.[User Link],
    tmp.pv,
    tmp.av,
    tmp.pr,
    tmp.ar
ORDER BY
    [Passive Rep] DESC
),
    
-- Create user table with account age and post count.
--declare @userTable table (id int, start DATETIME, posts int)
--insert @userTable
userTable (id, start, posts)
AS (
SELECT
    u.Id AS [User Link],
    u.CreationDate as start,
    Count(*) AS [Posts]
FROM
    Posts p
  INNER JOIN
    Users u ON u.Id = p.OwnerUserId
WHERE
    p.OwnerUserId is not null and
    p.posttypeid = 1 or p.posttypeid = 2
GROUP BY
    u.Id,
    u.CreationDate
)

-- Join everything together
SELECT TOP 1000
    row_number() over(order by t.pr desc) as [#], 
    t.id AS [User Link],
    CONVERT(VARCHAR(10),u.start,102) AS [Start Date],
    u.posts AS [Posts],
--    t.pv AS [Passive Votes],
    Cast(t.pv / @window AS DECIMAL(20, 3)) as [Passive Votes / Day],
--    t.av AS [Active Votes],
    Cast(t.av / @window AS DECIMAL(20, 3)) as [Active Votes / Day],
--    t.pr AS [Passive Rep],
    Cast(t.pr / @window AS DECIMAL(20, 3)) as [Passive Rep / Day],
--    t.ar AS [Active Rep]
    Cast(t.ar / @window AS DECIMAL(20, 3)) as [Active Rep / Day]
FROM rawTable t
  INNER JOIN
    userTable u ON t.id = u.id
ORDER BY
    [Passive Rep / Day] DESC

fork query

edited jan 25 16 jon.doe12636

created apr 18 13 Mysticial