4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

-- cf https://data.stackexchange.com/stackoverflow/query/882556/reputation-gained-until-a-certain-date

-- ANSWERS
WITH abd AS (SELECT
  v.CreationDate AS VoteDate,

-- Upvotes / downvotes received (downvotes given will be missing)
  SUM(CASE
    WHEN a.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 2
     AND (CONVERT(DATE, a.CommunityOwnedDate) > v.CreationDate
          OR a.CommunityOwnedDate IS NULL)
    THEN 10 
    WHEN a.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 3
     AND (CONVERT(DATE, a.CommunityOwnedDate) > v.CreationDate
          OR a.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS ReputationFromVotes,  

-- Bounties earned
  SUM(CASE
    WHEN a.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 9
    THEN v.BountyAmount
    ELSE 0
  END) AS ReputationFromBounties,

-- Answers accepted
  SUM(CASE
    WHEN q.OwnerUserId = ##UserId:int##
     AND a.OwnerUserId != ##UserId:int##
     AND v.VoteTypeId = 1
     AND (CONVERT(DATE, q.CommunityOwnedDate) > v.CreationDate
          OR q.CommunityOwnedDate IS NULL)
    THEN 2
    WHEN q.OwnerUserId != ##UserId:int##
     AND a.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 1
     AND (CONVERT(DATE, a.CommunityOwnedDate) > v.CreationDate
          OR a.CommunityOwnedDate IS NULL)
    THEN 15
    ELSE 0 
  END) AS ReputationFromAccepts

  FROM Posts AS a
  INNER JOIN Posts AS q ON a.ParentId = q.Id 
  INNER JOIN Votes AS v ON v.PostId = a.Id
  WHERE a.OwnerUserId = ##UserId:int## 
     OR q.OwnerUserId = ##UserId:int##

  GROUP BY v.CreationDate
),

-- QUESTIONS
qbd AS (SELECT
  v.CreationDate AS VoteDate,

-- Upvotes / downvotes received
  SUM(CASE
    WHEN q.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 2
     AND (CONVERT(DATE, q.CommunityOwnedDate) > v.CreationDate
          OR q.CommunityOwnedDate IS NULL)
    THEN 10
    WHEN q.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 3
     AND (CONVERT(DATE, q.CommunityOwnedDate) > v.CreationDate
          OR q.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS ReputationFromVotes

  FROM Posts AS q
  INNER JOIN Votes AS v ON v.PostId = q.Id
  WHERE q.OwnerUserId = ##UserId:int##
    AND q.PostTypeId = 1

  GROUP BY v.CreationDate
),

-- BOUNTIES
bbd AS (SELECT
  v.CreationDate AS VoteDate,

-- Bounties posted
  SUM(-v.BountyAmount) AS ReputationFromBounties
  
  FROM Votes AS v
  WHERE v.UserId = ##UserId:int##
    AND v.VoteTypeId = 8
    
  GROUP BY v.CreationDate
),

-- SUGGESTED EDITS
ebd AS (SELECT
  DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0) AS VoteDate,

-- Suggested edit approved
  2 * COUNT(*) AS ReputationFromSuggestedEdits

  FROM SuggestedEdits AS se
  INNER JOIN Posts AS p ON se.PostId = p.Id
  WHERE se.OwnerUserId = ##UserId:int##
    AND se.ApprovalDate IS NOT NULL
    AND se.RejectionDate IS NULL
-- This includes edits approved by now-deleted users,
-- but those are extremely hard to filter out.
-- It *ex*cludes edits on deleted posts which qualify for
-- reputation preservation (60 days old, 3+ score)
    
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0)
),
-- One cannot gain more than 1000 reputation by suggested edits.
-- However, it's impossible to figure out which gains are skipped
-- because of the daily reputation limit, so we don't attempt to
-- account for this.

-- DATES
dates AS (SELECT DISTINCT(CreationDate) AS [Date]
  FROM Votes
  WHERE CreationDate >= (SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CreationDate), 0)
  FROM Users
  WHERE Id = ##UserId##)
),

-- REPUTATION CHANGES
changes AS (SELECT dates.Date,
  SUM(CASE
    WHEN COALESCE(abd.ReputationFromVotes, 0)
       + COALESCE(qbd.ReputationFromVotes, 0)
       + COALESCE(ebd.ReputationFromSuggestedEdits, 0) > 200
    THEN 200
    ELSE COALESCE(abd.ReputationFromVotes, 0)
       + COALESCE(qbd.ReputationFromVotes, 0)
       + COALESCE(ebd.ReputationFromSuggestedEdits, 0)
    END
  + COALESCE(abd.ReputationFromBounties, 0)
  + COALESCE(abd.ReputationFromAccepts, 0)
  + COALESCE(bbd.ReputationFromBounties, 0)) AS Reputation
  FROM dates
  LEFT OUTER JOIN abd ON dates.Date = abd.VoteDate
  LEFT OUTER JOIN qbd ON dates.Date = qbd.VoteDate
  LEFT OUTER JOIN bbd ON dates.Date = bbd.VoteDate
  LEFT OUTER JOIN ebd ON dates.Date = ebd.VoteDate
  GROUP BY dates.Date
)

-- Association bonus and given downvotes to answers will be missing

SELECT c2.Date, 1 + SUM(c1.Reputation)
  FROM changes AS c1
  INNER JOIN changes AS c2
    ON c1.Date <= c2.Date
  GROUP BY c2.Date
  ORDER BY c2.Date

fork query

edited mar 10 22

created dec 21 19 Glorfindel