Q&A for scientists using computers to solve scientific problems
-- [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