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 for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

-- How much reputation are you getting from your past work?
-- 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

declare @UserId int = ##UserId##;

SELECT @UserId as [User Link], Reputation, AgeInDays AS AccountAgeInDays,CAST(RepFromPast AS float)/AgeInDays AS OldReputationPerDay
  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
                AND p.ownerUserId != parents.ownerUserId
          ) AS RepCounts
        ) As RepAndUserCount
      JOIN 
        (
        SELECT Id, Reputation, 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