0

Please login or register to vote for this query.

(click on this box to dismiss)

Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)

Q&A for music historians, critics, and fans

-- Top Users by Country and tag
-- Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)

SELECT
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
    u.Id AS [User Link],
    u.CreationDate,
    u.Reputation,
    u.EmailHash,
    t.TagName, 
    COUNT(p.id) AS [Tagged Posts]
FROM
    Users u
    INNER JOIN Posts p on p.ownerUserid = u.Id
    INNER JOIN PostTags pt on pt.PostId = p.Id
    INNER JOIN Tags t on t.Id = pt.TagId
WHERE
    LOWER(u.Location) LIKE LOWER('%##locationname##%')
  
GROUP BY u.Reputation, u.Id, u.CreationDate, u.EmailHash, t.Tagname

fork query

edited oct 3 18 anonymous

created nov 19 11 Samuel Liew