tag rankings by location

1

Please login or register to vote for this query.

(click on this box to dismiss)

Calculates rank by tag. Make tag name empty spaces for all tags

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

-- tag rankings
-- Calculates rank by tag. Make tag name empty spaces for all tags

DECLARE @tagName varchar(255) 
DECLARE @Location varchar(255) 
DECLARE @tagInput bit
SET @tagname = rtrim(##TagName:string##)
SET @location = CONCAT('%',rtrim(##Location:string##),'%')

IF RTRIM(@tagname) = ''

   SET @tagInput = 0 
ELSE
   SET @tagInput = 1
 

 
;WITH UserTagScores
as 
(SELECT  
   a.OwnerUserId,
   U.Location,
   t.TagName,
   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(Location) LIKE LOWER(@location)
GROUP BY
   a.OwnerUserId,
   t.TagName,
   U.Location)
, userTagRank as (
 SELECT
    TagName,
   OwnerUserId,
    rank() over (partition by TagName order by score desc) rank
  FROM
    UserTagScores)
SELECT 
    TagName,
    rank
FROM
    userTagRank
WHERE 
    OwnerUserId = ##UserId## 
ORDER BY
    TagName

Enter Parameters

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