Reputation changes by date

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

-- 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 (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL)
    THEN 10 
    WHEN a.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 3
     AND (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 (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 (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 (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL)
    THEN 5 
    WHEN q.OwnerUserId = ##UserId:int##
     AND v.VoteTypeId = 3
     AND (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 (tag wiki suggested edits will be missing)
  2 * COUNT(*) AS ReputationFromSuggestedEdits

  FROM SuggestedEdits AS se
  WHERE se.OwnerUserId = ##UserId##
    AND se.ApprovalDate IS NOT NULL
    
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0)
)

-- Association bonus will be missing

SELECT COALESCE(abd.VoteDate, qbd.VoteDate, bbd.VoteDate, ebd.VoteDate),
  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))
  FROM abd
  FULL OUTER JOIN qbd ON abd.VoteDate = qbd.VoteDate
  FULL OUTER JOIN bbd ON abd.VoteDate = bbd.VoteDate
  FULL OUTER JOIN ebd ON abd.VoteDate = ebd.VoteDate
  GROUP BY COALESCE(abd.VoteDate, qbd.VoteDate, bbd.VoteDate, ebd.VoteDate)
  ORDER BY COALESCE(abd.VoteDate, qbd.VoteDate, bbd.VoteDate, ebd.VoteDate)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results