Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ;WITH USER_BY_TAG
- AS
- (
- SELECT
- ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) Rank,
- u.Location,
- COUNT(*) AS UpVotes
- FROM Tags t
- INNER JOIN PostTags pt ON pt.TagId = t.id
- INNER JOIN Posts p ON p.ParentId = pt.PostId
- INNER JOIN Votes v ON v.PostId = p.Id and VoteTypeId = 2
- INNER JOIN Users u ON u.Id = p.OwnerUserId
- WHERE
- (LOWER(Location) LIKE '% germany%' OR
- LOWER(Location) LIKE '% spain%' OR
- LOWER(Location) LIKE '% holland%' OR
- LOWER(Location) LIKE '% france%' OR
- LOWER(Location) LIKE '% italy% ') OR
- LOWER(Location) LIKE '% netherland%' OR
- LOWER(Location) LIKE '% united kingdom%' OR
- LOWER(Location) LIKE '% poland%' OR
- LOWER(Location) LIKE '% sweden%'
- AND TagName = 'android'
- GROUP BY u.Location
- )
- SELECT * FROM USER_BY_TAG WHERE rank <= 1000 ORDER BY upvotes DESC ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement