0

Please login or register to vote for this query.

(click on this box to dismiss)

Put banks in for Tag Name for all. Tag names should be in lower case Put 0 for user ID for all users

Q&A for professional and enthusiast programmers

-- Tag users by tag and city

DECLARE @tagName varchar(255) 
DECLARE @tagInput bit
DECLARE @cityName varchar(255) 
SET @tagname = rtrim(##TagName:string##)
SET @cityName = rtrim(##cityName:string##);

IF @tagname = ''
  SELECT 'Missing tagname'
ELSE IF @tagname = '' OR @cityName = ''
  SELECT 'Missing city'
ELSE
SET @cityName = '%'+LOWER(@tagname)+'%';
 
WITH UserTagScores
as 
(SELECT  
   a.OwnerUserId,
   t.TagName,
   u.Location,
   u.Reputation,
   SUM(CASE v.VoteTypeID
           WHEN 2 THEN 1
           WHEN 3 THEN  -1
   END) as  score

FROM 
   votes v
   INNER JOIN Posts a
   ON v.PostID = a.ID
   INNER JOIN Posts q
   ON a.parentId = q.id
   INNER JOIN PostTags pt
   ON q.id = pt.PostId
   INNER JOIN Tags T
   ON pt.TagId = t.id
   INNER JOIN USERS u
   ON u.id = a.OwnerUserId
WHERE
   v.voteTypeId in (2,3)  -- Up and down votes
   and
   a.PostTypeID = 2  -- Answers only
   AND
   (a.CommunityOwnedDate is null or 
    a.CommunityOwnedDate > v.CreationDate)
   AND 
   (@tagInput = 0
    or t.TagName = @TagName)
    AND a.OwnerUserId is not null
    AND LOWER(u.Location) LIKE @cityName
GROUP BY
   a.OwnerUserId,
   t.TagName,
   u.Location,
   u.Reputation)
, userTagRank as (
 SELECT
    TagName,
   OwnerUserId,
   Location,
   Reputation,
    rank() over (partition by TagName order by score desc) rank,

Score
  FROM
    UserTagScores)
SELECT 
    TagName,
    Rank,
    OwnerUserId,
    Location,
    Reputation,
    OwnerUserId [User Link],
    Score
FROM
    userTagRank 

ORDER BY
    TagName,
    rank

fork query

edited sep 6 16 Alex.BelisleTurcot

created nov 15 11 conradfrix