0

Please login or register to vote for this query.

(click on this box to dismiss)

See https://meta.stackexchange.com/q/329452/148310 1) The input is a comma-separated list of user ID's. 2) The query finds each user's top 3 tags. 3) Then compares the specified users across all the tags found in step 2.

Q&A about the site for serious players and enthusiasts of poker

CREATE TABLE #TopTagsPerSelectUsers (
    UserId          INT
    , UserName      NVARCHAR(128)  -- Schema check was 40
    , TagId         INT
    , TagName       NVARCHAR(128)  -- Schema check was 35
    , tagScore      INT
    , numPosts      INT
);
DECLARE @queryStr   AS NVARCHAR (max);

WITH targetUsers AS (
    SELECT  u.Id  AS UserId
    FROM    Users u
    WHERE   u.Id IN (
        SELECT  DISTINCT CAST (TRIM (value) AS INT)
        FROM    STRING_SPLIT (##CSVOfUsers:STRING##, ',')  -- Underscores not allowed in SEDE names
        WHERE   TRIM (value) != ''
    )
),
eligiblePosts AS (
    SELECT      tu.UserId
                -- "Top Tags" *Score* only counts answers...
                , Score   = (
                    CASE    WHEN p.PostTypeId = 2  AND  p.CommunityOwnedDate IS NULL
                            THEN p.Score
                            ELSE 0
                    END
                )
                , parentQ = ( CASE WHEN p.PostTypeId = 2  THEN p.ParentId  ELSE p.Id  END )
                , postId  = p.Id
    FROM        targetUsers tu
    INNER JOIN  Posts p                 ON p.OwnerUserId = tu.UserId
    WHERE       p.PostTypeId            IN (1, 2) -- Q&A only
),
rankedTagsPerUser AS (
    SELECT      ep.UserId
                , pt.TagId
                , tagScore = SUM (ep.Score)
                , numPosts = COUNT (ep.postId)
                , Rank = DENSE_RANK() OVER (
                    PARTITION BY    ep.UserId
                    ORDER BY        SUM (ep.Score) DESC,
                                    COUNT (ep.postId) DESC
                )
    FROM        eligiblePosts ep
    INNER JOIN  PostTags pt             ON pt.PostId = ep.parentQ
    GROUP BY    ep.UserId, pt.TagId
),
distinctTags AS (
    SELECT DISTINCT tpu.TagId
    FROM            rankedTagsPerUser tpu
    WHERE           tpu.Rank <= 3
)

INSERT INTO #TopTagsPerSelectUsers
SELECT
            tpu.UserId
            , u.DisplayName
            , dt.TagId
            , t.TagName
            , tpu.tagScore
            , tpu.numPosts
FROM        distinctTags dt
INNER JOIN  rankedTagsPerUser tpu   ON tpu.TagId = dt.TagId
INNER JOIN  Tags t                  ON t.Id = tpu.TagId
INNER JOIN  Users u                 ON u.Id = tpu.UserId
;

;WITH uniqueTagSet AS (
    SELECT      ttpu.TagId
                , ttpu.TagName
                , tagMaxScore = MAX (ttpu.tagScore)
                , tagMaxPosts = MAX (ttpu.numPosts)
    FROM        #TopTagsPerSelectUsers ttpu
    GROUP BY    ttpu.TagId, ttpu.TagName
),
tagColmnNames AS (
    SELECT  tagIds = STUFF ( (
                    SELECT      N', ' + QUOTENAME (CAST (uts.TagId AS NVARCHAR) )
                    FROM        uniqueTagSet uts
                    ORDER BY    uts.tagMaxScore DESC, uts.tagMaxPosts DESC, uts.TagName
                    FOR XML PATH (N''), TYPE
                ).value (N'.[1]', N'nvarchar(max)'), 1, 2, N''
            )
            , tagColCode = STUFF ( (
                    SELECT      N', ISNULL ([' + CAST (uts.TagId AS NVARCHAR) + '], 0) AS ' + QUOTENAME (uts.TagName)
                    FROM        uniqueTagSet uts
                    ORDER BY    uts.tagMaxScore DESC, uts.tagMaxPosts DESC, uts.TagName
                    FOR XML PATH (N''), TYPE
                ).value (N'.[1]', N'nvarchar(max)'), 1, 2, N''
            )
)
SELECT      @queryStr = N'
                SELECT      [User] = UserLink
                            , [Max Tag Score] = UserRank
                            , [Category] = ''Scores''
                            , ' + tcn.tagColCode + N'
                FROM        (
                    SELECT  UserLink = ''site://users/'' + CAST(ttpu.UserId AS nvarchar(128)) + ''|'' + ttpu.UserName
                            , UserRank = (SELECT MAX (ur.tagScore)  FROM #TopTagsPerSelectUsers ur  WHERE ur.UserId = ttpu.UserId)
                            , ttpu.tagScore
                            , ttpu.TagId
                            , ttpu.UserName
                    FROM    #TopTagsPerSelectUsers ttpu
                ) foo
                PIVOT (
                    SUM (tagScore) -- IsNull and Coalesce do not seem to work here.
                    FOR TagId IN (' + tcn.tagIds + ')
                ) pvt

                UNION ALL SELECT
                            [User] = UserLink
                            , [Max Tag Score] = UserRank
                            , [Category] = ''Num Posts''
                            , ' + tcn.tagColCode + N'
                FROM        (
                    SELECT  UserLink = ''site://users/'' + CAST(ttpu.UserId AS nvarchar(128)) + ''|'' + ttpu.UserName
                            , UserRank = (SELECT MAX (ur.tagScore)  FROM #TopTagsPerSelectUsers ur  WHERE ur.UserId = ttpu.UserId)
                            , ttpu.numPosts
                            , ttpu.TagId
                            , ttpu.UserName
                    FROM    #TopTagsPerSelectUsers ttpu
                ) foo
                PIVOT (
                    SUM (numPosts) -- IsNull and Coalesce do not seem to work here.
                    FOR TagId IN (' + tcn.tagIds + ')
                ) pvt

                ORDER BY    [Max Tag Score] DESC, [User], [Category] DESC
            '
FROM        tagColmnNames tcn

EXEC sp_executesql @queryStr

DROP TABLE #TopTagsPerSelectUsers

view current version

edited jun 14 19

created jun 14 19 Brock Adams