WITH EUUsers AS ( SELECT Id, DisplayName, AboutMe, Location

0

Please login or register to vote for this query.

(click on this box to dismiss)

English Language Learners

Q&A for speakers of other languages learning English

-- Find EU Users
WITH EUUsers AS (
    SELECT
        Id,
        DisplayName,
        AboutMe,
        Location
    FROM Users
    WHERE (
        Location LIKE '%Austria%' OR
        Location LIKE '%Belgium%' OR
        Location LIKE '%Bulgaria%' OR
        Location LIKE '%Croatia%' OR
        Location LIKE '%Cyprus%' OR
        Location LIKE '%Czech Republic%' OR
        Location LIKE '%Denmark%' OR
        Location LIKE '%Estonia%' OR
        Location LIKE '%Finland%' OR
        Location LIKE '%France%' OR
        Location LIKE '%Germany%' OR
        Location LIKE '%Greece%' OR
        Location LIKE '%Hungary%' OR
        Location LIKE '%Ireland%' OR
        Location LIKE '%Italy%' OR
        Location LIKE '%Latvia%' OR
        Location LIKE '%Lithuania%' OR
        Location LIKE '%Luxembourg%' OR
        Location LIKE '%Malta%' OR
        Location LIKE '%Netherlands%' OR
        Location LIKE '%Poland%' OR
        Location LIKE '%Portugal%' OR
        Location LIKE '%Romania%' OR
        Location LIKE '%Slovakia%' OR
        Location LIKE '%Slovenia%' OR
        Location LIKE '%Spain%' OR
        Location LIKE '%Sweden%'
    )
    ORDER BY Id ASC
    OFFSET 0 ROWS
    FETCH NEXT 500 ROWS ONLY  
),
-- Find their tags
CombinedTags AS (
    SELECT 
        EUUsers.Id as UserId,
        EUUsers.DisplayName as DisplayName, 
        EUUsers.AboutMe as AboutMe,
        EUUsers.Location, 
        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,
        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 meta site
loading Hold tight while we fetch your results