Reputation when posting a question

0

Please login or register to vote for this query.

(click on this box to dismiss)

Sustainable Living

Q&A for folks dedicated to a lifestyle that can be maintained indefinitely without depleting available resources

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

CREATE TABLE #changes (date datetime, reputation int);

-- 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

INSERT INTO #changes
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);

SELECT p.Id AS [Post Link], p.CreationDate,
  SUM(c.Reputation) + CASE WHEN SUM(c.Reputation) >= 200 THEN 100 ELSE 0 END -- simulated association bonus
  FROM Posts AS p
  LEFT OUTER JOIN #changes AS c
    ON DATEADD(DAY, DATEDIFF(DAY, 0, p.CreationDate), 0) > c.Date
  WHERE p.PostTypeId = 1 -- Questions
    AND p.OwnerUserId = ##UserId:int##
  GROUP BY p.Id, p.CreationDate
  ORDER BY p.CreationDate;

DROP TABLE #changes;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results