0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and scholars of mythology and folklore

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 '%tehran%'
     )
   
      -- 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

fork query

edited oct 28 20

created oct 28 20 anonymous