4

Please login or register to vote for this query.

(click on this box to dismiss)

Take the "unexpected" reputation coming from your answers from last month. Suppose now you constantly get those, and figure out how much rep per day you are getting for your past work without "working for it" on that given day

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

-- Collecting votes from the past
-- This query will look for answers that got upvoted more than a month after they were posted

declare @UserId int = ##UserId##;

--SELECT CAST(RepFromPast AS float)/AgeInDays AS OldReputationPerDay
SELECT *
  FROM 
      (
      SELECT @UserId AS Id, SUM(Reputation) AS RepFromPast 
        FROM (
            SELECT CASE WHEN VoteTypeId = 2 THEN 10 ELSE -2 END AS Reputation    
              FROM
                Votes v JOIN Posts p ON v.PostId=p.Id JOIN Posts parents ON p.ParentId=parents.Id
              WHERE p.PostTypeId = 2
                AND p.OwnerUserId = @UserId
                AND v.VoteTypeId In (2,3)
                AND datediff(day, p.CreationDate,v.CreationDate) > 30
          ) AS RepCounts
        ) As RepAndUserCount
      JOIN 
        (
        SELECT Id, CONVERT(int, GETDATE() - CreationDate) as AgeInDays
          FROM Users
          WHERE Users.Id = @UserId
        ) AS AccountAge
      ON RepAndUserCount.Id=AccountAge.Id

view current version

edited jul 7 12

created jul 7 12 Miquel