Top Users by Tag and Location

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

-- Top Users by Tag and Location
-- based on answers

DECLARE @Tag AS NVARCHAR(64) = LOWER(RTRIM(LTRIM(##Tag:string?rxjs##)))
DECLARE @Location AS NVARCHAR(64) = LOWER(RTRIM(LTRIM(##Location:string?ukraine##)))
DECLARE @Empty AS BIT = ##Empty:int?0##

SELECT TOP 100 
  u.Id AS [User Link],
  u.Location,
--  a.Id AS [Post Link],
  COUNT(a.Id) as xCount,
  SUM(a.Score) as xSum
FROM Posts AS a
  JOIN Posts AS q on a.ParentId = q.Id
  JOIN Users AS u on a.OwnerUserId = u.Id
WHERE
  a.PostTypeId = '2'
  AND LOWER(q.Tags) LIKE '%'+@Tag+'%'
  AND (
    @Empty = 0 
    OR u.Location is NULL
    OR u.Location = ''
    OR LOWER(u.Location) LIKE '%'+@Location+'%'
  )
  AND (
    @Empty = 1 
    OR LOWER(u.Location) LIKE '%'+@Location+'%'
  )
GROUP BY
  u.Id, u.Location
-- HAVING
--   SUM(a.Score) >= 5
ORDER BY
  xSum DESC

Enter Parameters

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