User in the top n answerers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #Tags
(
    TagId integer NOT NULL PRIMARY KEY
);

-- Tags the user participated in
INSERT #Tags WITH (TABLOCK)
    (TagId)
SELECT DISTINCT
    PT.TagId
FROM dbo.Posts AS P
JOIN dbo.PostTags AS PT
    ON PT.PostId = P.ParentId -- the question has the tags
WHERE
    P.PostTypeId = 2 -- Answer
    AND P.OwnerUserId = ##userid?7031230##;
    
CREATE TABLE #TagPosts
(
    TagId integer NOT NULL,
    PostId integer NOT NULL,
    
    INDEX CCS CLUSTERED COLUMNSTORE
);

-- All posts in the target user's tags
INSERT #TagPosts WITH (TABLOCK)
(
    TagId,
    PostId
)
SELECT
    T.TagId,
    PT.PostId
FROM #Tags AS T
JOIN dbo.PostTags AS PT
    ON PT.TagId = T.TagId;

CREATE TABLE #OwnerTagScores
(
    OwnerUserId integer NOT NULL,
    TagId integer NOT NULL,
    TagScore integer NOT NULL,
    Pos bigint NOT NULL
);

-- Scores and rankings for the selected tags, per user
INSERT #OwnerTagScores WITH (TABLOCK)
(
    OwnerUserId,
    TagId,
    TagScore,
    Pos
)
SELECT
    OwnerUserId,
    TagId,
    TagScore,
    Pos
FROM
(
    SELECT
        P.OwnerUserId,
        TP.TagId,
        TagScore = SUM(P.Score),
        Pos = RANK() OVER (
            PARTITION BY TP.TagId
            ORDER BY SUM(P.Score) DESC)
    FROM #TagPosts AS TP
    JOIN dbo.Posts AS P WITH (FORCESCAN)
        ON P.ParentId = TP.PostId
    WHERE
        P.PostTypeId = 2
        AND P.OwnerUserId IS NOT NULL
    GROUP BY
        P.OwnerUserId,
        TP.TagId
) AS TagScorePos
WHERE
    TagScorePos.Pos <= ##pos?10##;
    
-- Details for the target user
SELECT
    T.TagName,
    OTS.Pos,
    OTS.TagScore
FROM #OwnerTagScores AS OTS
JOIN dbo.Tags AS T
    ON T.Id = OTS.TagId
WHERE
    OTS.OwnerUserId = ##userid?7031230##
ORDER BY
    OTS.Pos,
    T.TagName;

Enter Parameters

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