0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- [r]  top 1000 users
-- Top 1000 by votes users in tag [r]
DECLARE @TagName nvarchar(25) = 'r';

WITH topusers AS(
    SELECT TOP 1000
        u.id, 
        COUNT(*) AS UpVotes
    FROM Tags t with (nolock)
        INNER JOIN PostTags pt with (nolock)ON pt.TagId = t.id
        INNER JOIN Posts p with (nolock) ON p.ParentId = pt.PostId
        INNER JOIN Votes v with (nolock) ON v.PostId = p.Id and v.VoteTypeId = 2
        inner join Users u with (nolock) on u.Id = p.OwnerUserId
    WHERE 
        p.CommunityOwnedDate IS NULL
        and t.TagName = @TagName
    GROUP BY u.Id
    ORDER BY UpVotes DESC
)
SELECT
    topusers.id,
    u.DisplayName,
    topusers.UpVotes,
    u.location
FROM topusers
JOIN Users u on (topusers.id = u.id)
ORDER BY UpVotes DESC

fork query

edited mar 2 13 jon.doe10614

created mar 2 13 Andrie