Gets a list of most reputed users from a given location.
Q&A about the site for administrators, end users, developers and designers for Craft CMS
SELECT * FROM (SELECT TOP 100 'Gold' AS [Type], UserId AS [User Link], Count(*) as [Count] FROM Badges b INNER JOIN users u ON u.id = b.userid WHERE location like '%##Location##%' AND Class=1 GROUP BY UserId ORDER BY [Count] DESC ) g UNION ALL SELECT * FROM ( SELECT TOP 10 'Silver' AS [Type], UserId AS [User Link], Count(*) as [Count] FROM Badges b INNER JOIN users u ON u.id = b.userid WHERE location like '%##Location##%' AND Class=2 GROUP BY UserId ORDER BY [Count] DESC ) s UNION ALL SELECT * FROM ( SELECT TOP 10 'Bronze' AS [Type], UserId AS [User Link], Count(*) as [Count] FROM Badges b INNER JOIN users u ON u.id = b.userid WHERE location like '%##Location##%' AND Class=3 GROUP BY UserId ORDER BY [Count] DESC ) b