1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for serious players and enthusiasts of chess

-- Does not currently include:
--   - Reputation for accepting/unaccepting answers to your questions
--   - Bounties paid
--   - Rep loss from downvoting others people's answers
--   - Reputation loss from Spam or Rude/Abusive flags

-- UserId: User ID
DECLARE @UserId int = ##UserId:int##;
-- AssociationBonus: Association bonus
DECLARE @AssociationBonus int = ##AssociationBonus:int?100##;
-- VoteCutoff1: First n votes
DECLARE @VoteCutoff1 int = ##VoteCutoff1:int?10##;
-- RepValue1: are worth:
DECLARE @RepValue1 int = ##RepValue1:int?10##;
-- VoteCutoff2: Second n votes
DECLARE @VoteCutoff2 int = ##VoteCutoff2:int?10##;
-- RepValue2: are worth:
DECLARE @RepValue2 int = ##RepValue2:int?5##;
-- VoteCutoff3: Third n votes
DECLARE @VoteCutoff3 int = ##VoteCutoff3:int?20##;
-- RepValue3: are worth:
DECLARE @RepValue3 int = ##RepValue3:int?2##;
-- RepValue4: Last votes are worth:
DECLARE @RepValue4 int = ##RepValue4:int?1##;
-- AcceptedEditRep: Accepted edit rep:
DECLARE @AcceptedEditRep int = ##AcceptedEditRep:int?2##;

DECLARE @Offset1 int = @VoteCutoff1 * @RepValue1;
DECLARE @Offset2 int = @Offset1 + @VoteCutoff2 * @RepValue2;
DECLARE @Offset3 int = @Offset2 + @VoteCutoff3 * @RepValue3;

WITH myposts AS (
  SELECT
    p.Id as Id,
    sum(case when v.VoteTypeId = 2 then 1 else 0 end) as Upvotes,
    sum(case when v.VoteTypeId = 3 then 1 else 0 end) as Downvotes,
    sum(case when v.VoteTypeId = 1 then 1 else 0 end) as Accepts,
    sum(case when v.VoteTypeId = 9 then v.BountyAmount else 0 end) as Bounty
  FROM
    Posts p
  INNER JOIN Votes as v ON
    p.OwnerUserId = @UserID AND
    v.PostId = p.Id
  LEFT JOIN Posts as mine ON -- Filter accepts of self-answers
    mine.OwnerUserId = @UserID AND
    v.VoteTypeId = 1 AND -- Accepted answer
    p.ParentId = mine.Id
  WHERE
    mine.Id IS NULL
  GROUP BY
    p.Id
),

scorecomp AS (
  SELECT
    Id,
    Upvotes,
    Downvotes,
    Accepts,
    Bounty,
    (SELECT TOP 1 (myposts.Upvotes - Mapping.Votes) * Mapping.Score + Mapping.Offset
      FROM 
        (VALUES 
          (0,                                          @RepValue1, 0),
          (@VoteCutoff1,                               @RepValue2, @Offset1),
          (@VoteCutoff1 + @VoteCutoff2,                @RepValue3, @Offset2),
          (@VoteCutoff1 + @VoteCutoff2 + @VoteCutoff3, @RepValue4, @Offset3)
        ) AS Mapping(Votes, Score, Offset)
      WHERE myposts.Upvotes >= Mapping.Votes
      ORDER BY Mapping.Votes DESC) + myposts.Accepts * 15 + myposts.Bounty as RepCap,
    Upvotes * 10 - Downvotes * 2 + Accepts * 15 + Bounty as CurrentRep
  FROM
    myposts
),

edits as (SELECT
  1 as Each
FROM
  SuggestedEdits e
INNER JOIN
  Posts p
ON
  e.PostId = p.Id
WHERE
  e.OwnerUserId = ##UserId##
  AND ApprovalDate IS NOT NULL
)

SELECT
  u.Id as [User Link],
  sum(IIF(RepCap >= CurrentRep, CurrentRep, RepCap)) + (SELECT coalesce(sum(Each), 0) from edits) * @AcceptedEditRep + 1 + @AssociationBonus as [New Reputation],
  sum(CurrentRep) + (SELECT coalesce(sum(Each), 0) from edits) * 2 + 1 + @AssociationBonus as [Computed Current Rep],
  u.Reputation as [Current Reputation]
FROM
  scorecomp
JOIN Users as u
  ON u.ID = @UserID
GROUP BY
  u.Id, u.Reputation

fork query

edited jul 1

created jun 27 jon.doe33355