Top Iranian users in specific tag
Q&A for programming puzzle enthusiasts and code golfers
SELECT TOP 100 u.Id as [User Link] ,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 v.VoteTypeId = 2 inner join Users u on u.Id = p.OwnerUserId WHERE p.CommunityOwnedDate IS NULL and lower(t.TagName) = ##tagname:string## and ( lower(Location) LIKE ('%iran%') OR Location LIKE '%IR' OR LOWER(Location) LIKE '%ahvaz%' --Do not use %ah%az% - this includes Bahia, Brazil OR LOWER(Location) LIKE '%ahwaz%' OR LOWER(Location) LIKE '%alborz%' OR LOWER(Location) LIKE '%ajabshir%' OR LOWER(Location) LIKE '%ardebil%' OR LOWER(Location) LIKE '%azarbaijan%' OR lower(Location) LIKE lower('%Babol%') OR LOWER(Location) LIKE '%bafq%' OR LOWER(Location) LIKE '%bushehr%' OR LOWER(Location) LIKE '%esfahan%' OR LOWER(Location) LIKE '%qom%' OR LOWER(Location) LIKE '%golestan%' OR LOWER(Location) LIKE '%gorgan%' OR LOWER(Location) LIKE '%ham%dan%' OR LOWER(Location) LIKE '%jahrom%' OR LOWER(Location) LIKE '%karaj%' OR LOWER(Location) LIKE '%kashan%' OR LOWER(Location) LIKE '%kerman%' -- includes kermanshah OR LOWER(Location) LIKE '%mashhad%' OR LOWER(Location) LIKE '%mehrshahr%' OR LOWER(Location) LIKE '%qazvin%' OR LOWER(Location) LIKE '%semnan%' OR LOWER(Location) LIKE '%shiraz%' OR LOWER(Location) LIKE '%tabriz%' OR LOWER(Location) LIKE '%tehran%' OR LOWER(Location) LIKE '%urmia%' OR LOWER(Location) LIKE '%yazd%' OR LOWER(Location) LIKE '%zanjan%' ) -- exclusions and not ( Location LIKE '%IZMIR%' --Turkey or Location LIKE 'ADHAMI PIRAN JHANDUKHEL BANNU' --Pakistan or lower(Location) LIKE lower('%Altamirano%') --Mexico - Spam account or lower(Location) LIKE lower('%Indiranagar%') --India or lower(Location) LIKE lower('%Mirandela%') --Portugal or lower(Location) LIKE lower('%Mirandola%') --Italy or lower(Location) LIKE lower('%Mirano%') --Italy or lower(Location) LIKE lower('%Pietravairano%') --Italy or lower(Location) LIKE lower('%Sapiranga%') --Brazil or lower(Location) LIKE lower('%Srirangam%') --India or lower(Location) LIKE lower('%Tirana%') --Albania or lower(Location) LIKE lower('%Tirane%') --Albania or lower(Location) LIKE lower('%Titirangi%') --New Zealand ) and LEN(Location) > 1 GROUP BY u.Id ORDER BY UpVotes DESC