Created
April 12, 2017 19:21
-
-
Save alanhay/9693d6dd49ba050b3e7e26f1563537b5 to your computer and use it in GitHub Desktop.
Stackoverflow Query
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Top 100 Users In A Given Tag | |
| -- | |
| -- What better way to learn interesting things about tags you are interested in than to check out the top 100 users in that tag? | |
| -- | |
| -- Inspired by this query: | |
| -- | |
| -- http://data.stackexchange.com/stackoverflow/s/1688/c-top100-users | |
| DECLARE @TagName nvarchar(25) = ##Tag:string## | |
| SELECT TOP 100 | |
| u.Id as [User Link], | |
| 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 | |
| and LOWER(Location) LIKE LOWER('%##Location##%') | |
| GROUP BY u.Id | |
| ORDER BY UpVotes DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment