2

I want to reproduce the top tags overview for multiple users with the Data Explorer:

enter image description here

In the following I simplify for two users. The required solution should be able to scale to 50+ users.

What I found is that I can filter for a user and get the tags per post.

SELECT t.* FROM
(
SELECT Tags FROM
Posts
WHERE Id IN 
(
SELECT
    ParentId
FROM
    Posts
WHERE
    OwnerUserId = 3502164
    )
) AS t

What did I try?

Well usually the way would be to go with Count(tags), but the tags don't seem to be text. I found that one could attempt to link the results to the "tag-table", but I just got a lot of messy SQL code.

Sample desired Output:

enter image description here

1

TLDR: See this query.
It produces results like:

query results


Note:

  1. "Top Tags" Score only counts answers, while Posts counts both Q&A.
  2. "Top Tags" rank is computed by score. Ties are broken by post-count.
  3. Community wiki posts ARE included in the post count, but only score earned before CW status counts.

To accept a CSV string of user ID's for input, use SEDE SQL like:

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) != ''
)

Then get eligible posts like:

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) != ''
    )
)
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

Then the ranked tags per user:

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
)

The next part is tricky because you want a pivot table, but we don't know the name or number of columns in advance.
That means dynamic SQL is required and a temp table helps a lot.

So for all that, see the final query on SEDE.

2

The Tags field isn't really fit for analysis like this, and furthermore it's only filled for questions. You should really join with the PostTags and Tags table. I suspect you do like to experiment yourself a bit, so here is a little help in this regard: a SEDE query listing a single user's score and # of posts in each tag. It pretty much matches my profile:

  • thank you for the fast Reply. You are right i dont expect someone to do the coding for me. Its a way better start than mine (+1). Do you have a hint how i can scale across multiple users 50, 100+? – ThanksGuys Jun 13 at 9:42
  • I'm not sure how you would like to enter those IDs (maybe as comma-separated values), but you'd need to change both instances of OwnerUserId = ##UserId:int## into OwnerUserId IN (<something>), and add these OwnerUserIds to the respective GROUP BY clauses and the FULL OUTER JOIN. Then, you can probably do some magic with PIVOT, but that takes too much time for me to figure out right now. – Glorfindel Jun 13 at 9:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .