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

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

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,
    
    INDEX CCS CLUSTERED COLUMNSTORE
);

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##;
    
RETURN;

;with top_answerers as (
-- determine rank for each user per tag
select owneruserid
     , tagid
     , sum(score) [tag score]
     , rank() over(partition by tagid order by sum(score) desc ) as pos
from #postscore
group by owneruserid
       , tagid
)

/*
-- this was the original
;with top_answerers as (
select a.owneruserid
     , tgs.tagid
     , sum(a.score) [tag score]
     , rank() over(partition by tgs.tagid order by sum(a.score) desc ) as pos
from posts a
-- inner join posts q on q.id = a.parentid
inner join posttags pt on pt.postid = a.parentid -- q.id
inner join #tags tgs on tgs.tagid = pt.tagid
where a.posttypeid = 2 -- answer
-- and q.posttypeid = 1 -- Question
and a.owneruserid is not null 
group by a.owneruserid
       , tgs.tagid
)
*/

-- final query to select the specific user
select tagname
     , pos
     , owneruserid -- as [User Link]
     , [tag score]
from top_answerers
inner join tags t on tagid = t.id
where  owneruserid = ##userid?7031230##
and pos <= ##pos?10##
order by pos desc

Enter Parameters

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