List of Tags of users questions and answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Musical Practice and Performance Meta

Q&A about the site for musicians, students, and enthusiasts

-- Find EU Users
WITH EUUsers AS (
    SELECT
        Id,
        DisplayName,
        AboutMe,
        Location
    FROM Users
    WHERE (
        Location LIKE '%Europe%' OR
        Location LIKE '%EU%'
    )
),
CombinedTags AS (
    SELECT 
        EUUsers.Id as UserId,
        EUUsers.DisplayName as DisplayName, 
        EUUsers.AboutMe as AboutMe,
        EUUsers.Location,  -- corrected this line
        Tags.TagName
    FROM Comments
    INNER JOIN Posts ON Posts.Id = Comments.PostId
    INNER JOIN Posts AS PostsP ON PostsP.Id = Posts.ParentId
    INNER JOIN PostTags ON PostTags.PostId = PostsP.Id
    INNER JOIN Tags ON PostTags.TagId = Tags.Id
    INNER JOIN EUUsers ON EUUsers.Id = Comments.UserId

    UNION ALL

    SELECT 
        EUUsers.Id as UserId,
        EUUsers.DisplayName as DisplayName, 
        EUUsers.AboutMe as AboutMe,
        EUUsers.Location,  -- corrected this line
        Tags.TagName
    FROM EUUsers
    INNER JOIN Posts ON Posts.OwnerUserId = EUUsers.Id
    INNER JOIN PostTags ON PostTags.PostId = Posts.Id
    INNER JOIN Tags ON PostTags.TagId = Tags.Id
)

-- Aggregate their tags
SELECT 
    UserId,
    DisplayName, 
    AboutMe,
    Location,
    STRING_AGG(CAST(TagName AS NVARCHAR(MAX)), ';') AS Tags
FROM CombinedTags
GROUP BY UserId, DisplayName, AboutMe, Location
ORDER BY UserId ASC

Enter Parameters

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