User reputation excluding reputation earned on a certain tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

User reputation excluding reputation earned on a certain tag

Stack Overflow

Q&A for programmers

-- User reputation excluding reputation earned on a certain tag

DECLARE @UserId        int = ##UserId##
DECLARE @Tag           nvarchar(25) = '##Tag##';

DECLARE @MyQuestionUpped     int = 0; --  +5
DECLARE @MyQuestionDowned    int = 0; --  -2
DECLARE @MyAnswerUpped       int = 0; -- +10
DECLARE @MyAnswerDowned      int = 0; --  -2
DECLARE @MyAnswerAccepted    int = 0; -- +15
DECLARE @TheirQuestionUpped  int = 0; --   0
DECLARE @TheirQuestionDowned int = 0; --   0
DECLARE @TheirAnswerUpped    int = 0; --   0
DECLARE @TheirAnswerDowned   int = 0; --  -1
DECLARE @TheirAnswerAccepted int = 0; --  +2

DECLARE @TagScore      int;
DECLARE @UserRep       int;

SELECT
    @MyQuestionUpped = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS Q ON Q.Id = PostTags.PostId and Q.PostTypeId = 1
    INNER JOIN Votes AS U ON U.PostId = Q.Id and U.VoteTypeId = 2
WHERE
    Tags.TagName = @Tag and
    Q.OwnerUserId = @UserId

SELECT
    @MyQuestionDowned = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS Q ON Q.Id = PostTags.PostId and Q.PostTypeId = 1
    INNER JOIN Votes AS D ON D.PostId = Q.Id and D.VoteTypeId = 3
WHERE
    Tags.TagName = @Tag and
    Q.OwnerUserId = @UserId

SELECT
    @MyAnswerUpped = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS A ON A.ParentId = PostTags.PostId and A.PostTypeId = 2
    INNER JOIN Votes AS U ON U.PostId = A.Id and U.VoteTypeId = 2
WHERE
    Tags.TagName = @Tag and
    A.OwnerUserId = @UserId

SELECT
    @MyAnswerDowned = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS A ON A.ParentId = PostTags.PostId and A.PostTypeId = 2
    INNER JOIN Votes AS U ON U.PostId = A.Id and U.VoteTypeId = 3
WHERE
    Tags.TagName = @Tag and
    A.OwnerUserId = @UserId

SELECT
    @MyAnswerAccepted = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS A ON A.ParentId = PostTags.PostId and A.PostTypeId = 2
    INNER JOIN Posts AS Q ON Q.Id = A.ParentId and Q.AcceptedAnswerId = A.Id
WHERE
    Tags.TagName = @Tag and
    A.OwnerUserId = @UserId

SELECT
    @TheirQuestionUpped = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS Q ON Q.Id = PostTags.PostId and Q.PostTypeId = 1
    INNER JOIN Votes AS U ON U.PostId = Q.Id and U.VoteTypeId = 2
WHERE
    Tags.TagName = @Tag and
    U.UserId = @UserId -- U.UserId IS NOT EXPOSED -- ALWAYS NULL

SELECT
    @TheirQuestionDowned = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS Q ON Q.Id = PostTags.PostId and Q.PostTypeId = 1
    INNER JOIN Votes AS D ON D.PostId = Q.Id and D.VoteTypeId = 3
WHERE
    Tags.TagName = @Tag and
    D.UserId = @UserId -- D.UserId IS NOT EXPOSED -- ALWAYS NULL

SELECT
    @TheirAnswerUpped = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS A ON A.Id = PostTags.PostId and A.PostTypeId = 2
    INNER JOIN Votes AS U ON U.PostId = A.Id and U.VoteTypeId = 2
WHERE
    Tags.TagName = @Tag and
    U.UserId = @UserId -- U.UserId IS NOT EXPOSED -- ALWAYS NULL

SELECT
    @TheirAnswerDowned = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS A ON A.Id = PostTags.PostId and A.PostTypeId = 2
    INNER JOIN Votes AS D ON D.PostId = A.Id and D.VoteTypeId = 2
WHERE
    Tags.TagName = @Tag and
    D.UserId = @UserId -- D.UserId IS NOT EXPOSED -- ALWAYS NULL

SELECT
    @TheirAnswerAccepted = COUNT(*)
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.Id
    INNER JOIN Posts AS Q ON Q.Id = PostTags.PostId and Q.PostTypeId = 1
WHERE
    Tags.TagName = @Tag and
    Q.OwnerUserId = @UserId and
    Q.AcceptedAnswerId IS NOT NULL

SELECT
    @UserRep = Reputation
FROM
    Users
WHERE
    Users.Id = @UserId

SET @TagScore = @MyQuestionUpped     *  5 +
                @MyQuestionDowned    * -2 +
                @MyAnswerUpped       * 10 +
                @MyAnswerDowned      * -2 +
                @MyAnswerAccepted    * 15 +
                @TheirQuestionUpped  *  0 + -- NOT EXPOSED
                @TheirQuestionDowned *  0 + -- NOT EXPOSED
                @TheirAnswerUpped    *  0 + -- NOT EXPOSED
                @TheirAnswerDowned   * -1 + -- NOT EXPOSED
                @TheirAnswerAccepted *  2

SELECT
    @UserRep - @TagScore AS AdjustedRep,
    @MyQuestionUpped     AS MQU,
    @MyQuestionDowned    AS MQD,
    @MyAnswerUpped       AS MAU,
    @MyAnswerDowned      AS MAD,
    @MyAnswerAccepted    AS MAA,
    @TheirQuestionUpped  AS TQU,
    @TheirQuestionDowned AS TQD,
    @TheirAnswerUpped    AS TAU,
    @TheirAnswerDowned   AS TAD,
    @TheirAnswerAccepted AS TAA

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached