WITH Base AS ( SELECT TOP 100000000000000000 u.Id, u.Loca...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

WITH Base AS (
SELECT TOP 100000000000000000 u.Id, u.Location, t.TagName, YEAR(p.CreationDate) AS [Year], 
  COUNT(1) AS [Count]
FROM Posts p
JOIN PostTags pt ON pt.PostId = p.Id
JOIN Tags t ON t.Id = pt.TagId
JOIN Users u On u.Id = p.OwnerUserId
WHERE UPPER(Location) like UPPER('%CAMEROON%')
            or location like '%cameroon%'
            or location like '%Cameroon%' 
            or location like '%Cameroun%'
            or Lower (location) like '%cameroun%'
            or Upper(location) like '%CAMEROUN%'
GROUP BY u.Id, u.Location, t.TagName, YEAR(p.CreationDate)
ORDER BY [Count] DESC
),
Results AS (
SELECT Id,Location, TagName, [Year], [Count], 
  MAX([Count]) OVER (PARTITION BY Location, [Year]) AS [Max]
FROM Base
)
SELECT Id  AS [User Link], Location, TagName, [Year], [Max], [Count]
FROM Results
WHERE [Max] = [Count]
ORDER BY Location, [Year] DESC, [Max] DESC
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results