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.

Q&A about Stack Overflow, Server Fault and Super User

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

fork query

created jun 16 11 Jon Seigel