Test Query Uwe

0

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

-- Define the tags of interest
DECLARE @Tag1 NVARCHAR(50) = 'java'
DECLARE @Tag2 NVARCHAR(50) = 'python'
DECLARE @Tag3 NVARCHAR(50) = 'javascript'

-- Define the location of interest
DECLARE @Location NVARCHAR(100) = 'Berlin'

-- Get users with at least one post in each of the specified tags
SELECT DISTINCT
    u.Id AS [User Link],
    u.DisplayName,
    u.Location,
    u.Reputation,
    u.CreationDate
FROM
    Users u
    JOIN Posts p ON u.Id = p.OwnerUserId
    JOIN PostTags pt ON p.Id = pt.PostId
    JOIN Tags t ON pt.TagId = t.Id
WHERE
    u.Location LIKE '%' + @Location + '%' AND
    (t.TagName = @Tag1 OR t.TagName = @Tag2 OR t.TagName = @Tag3)
GROUP BY
    u.Id,
    u.DisplayName,
    u.Location,
    u.Reputation,
    u.CreationDate
HAVING
    COUNT(DISTINCT CASE WHEN t.TagName = @Tag1 THEN t.TagName END) > 0 AND
    COUNT(DISTINCT CASE WHEN t.TagName = @Tag2 THEN t.TagName END) > 0 AND
    COUNT(DISTINCT CASE WHEN t.TagName = @Tag3 THEN t.TagName END) > 0
ORDER BY
    u.Reputation DESC

Enter Parameters

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