My Tags (on Profile Page)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gets all the data for the My Tags area on your profile page.

Meta Stack Exchange

Q&A about the Stack Exchange Network

-- My Tags (on Profile Page)
-- Gets all the data for the My Tags area on your profile page.

DECLARE @userId int = ##UserId##

DECLARE @results table
(
    Tag nvarchar(25) NOT NULL,
    PostTypeId tinyint NOT NULL,
    NumberOfPosts int NOT NULL,
    TotalScore int NOT NULL
)

INSERT INTO @results
    SELECT
        t.TagName,
        p.PostTypeId,
        COUNT(1) AS NumberOfPosts,
        SUM(p.Score) AS TotalScore
        FROM Posts p
        INNER JOIN PostTags pt ON pt.PostId = COALESCE(p.ParentId, p.Id)
        INNER JOIN Tags t ON t.Id = pt.TagId
        WHERE
            (p.OwnerUserId = @userId) AND
            (p.CommunityOwnedDate IS NULL)
        GROUP BY
            t.TagName,
            p.PostTypeId

SELECT
    COALESCE(r1.Tag, r2.Tag) AS Tag,
    COALESCE(r1.NumberOfPosts, 0) AS Answers,
    COALESCE(r1.TotalScore, 0) AS AnswerScore,
    COALESCE(r2.NumberOfPosts, 0) AS Questions,
    COALESCE(r2.TotalScore, 0) AS QuestionScore
    FROM @results r1
    FULL OUTER JOIN @results r2 ON (r2.Tag = r1.Tag) AND (r2.PostTypeId != r1.PostTypeId)
    WHERE r1.PostTypeId = 2
    ORDER BY
        AnswerScore DESC,
        Answers DESC,
        QuestionScore DESC,
        Questions DESC

Enter Parameters

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