0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- The reputation values shown ignore the daily reputation cap.

DECLARE @minAgeDays INT, @marginDays INT, @lastDate DATETIME
SET @minAgeDays = 60
SET @marginDays = 30
SET @lastDate = (SELECT MAX(CreationDate) FROM Votes)

;WITH
  repAdjustments AS (
    SELECT
      p.Id AS PostId
    , Coalesce(p.Title, q.Title) AS Title
    , p.CreationDate
    , pt.Name AS Type
    , p.Score
    , u.DisplayName AS "User"
    , u.Id AS UserId
    , vt.Name AS Event
    , v.CreationDate AS EventDate
    , CASE vt.Name
        WHEN 'UpMod' THEN CASE pt.Name WHEN 'Question' THEN 5 ELSE 10 END
        WHEN 'DownMod' THEN -2
        WHEN 'AcceptedByOriginator' THEN 15
        WHEN 'BountyClose' THEN v.BountyAmount
      END AS Rep
    FROM Posts AS p
    INNER JOIN Votes AS v
      ON v.PostId = p.Id
    INNER JOIN VoteTypes AS vt
      ON vt.Id = v.VoteTypeId
      AND vt.Name IN ('UpMod', 'DownMod', 'AcceptedByOriginator', 'BountyClose')
    INNER JOIN PostTypes AS pt
      ON pt.Id = p.PostTypeId
      AND pt.Name IN ('Question', 'Answer')
    INNER JOIN Users AS u
      ON u.Id = p.OwnerUserId
    LEFT JOIN Posts AS q
      ON q.Id = p.ParentId
  )
, reps AS (
    SELECT
      SUBSTRING(Type, 1, 1) AS T
    , PostId AS "Post Link"
    , DATEDIFF(DAY, CreationDate, @lastDate) AS Age
    , Score
    , UserId AS "User Link"
    , SUM(Rep) AS Rep
    , SUM(CASE WHEN DATEDIFF(DAY, CreationDate, EventDate) <= @marginDays THEN Rep ELSE 0 END) AS EarlyRep
    , SUM(CASE WHEN DATEDIFF(DAY, EventDate, @lastDate) <= @marginDays THEN Rep ELSE 0 END) AS RecentRep
    FROM repAdjustments
    GROUP BY PostId
    , Title
    , CreationDate
    , Type
    , Score
    , UserId
  )
, rates AS (
    SELECT
      *
    , CAST(1.0 * Rep / Age AS DECIMAL(20, 3)) AS Daily
    , CAST(1.0 * EarlyRep / @marginDays AS DECIMAL(20, 3)) AS EarlyDaily
    , CAST(1.0 * RecentRep / @marginDays AS DECIMAL(20, 3)) AS RecentDaily
    FROM reps
  )
SELECT TOP 50
  *
FROM rates
WHERE Age > @minAgeDays
AND RecentRep > 0
ORDER BY Rep DESC

fork query

edited nov 22 12

created nov 19 12 WReach