All User Tag Ranks by Location

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @location varchar(255)
DECLARE @userId varchar(255)
SET @location = LOWER(CONCAT('%',rtrim(##CityName:string##),'%'))
SET @userId = rtrim(##UserId:string##)

SELECT V.tag
    , V.AnswerRank as 'Rank By # of Answers'
    , V.ScoreRank as 'Rank By Answer Score'
FROM
(
  SELECT R.tag
    ,R.OwnerUserId
    ,R.answerScore
    ,RANK() over (partition by R.tag order by R.answerCount desc) as AnswerRank
    ,RANK() over (partition by R.tag order by R.answerScore desc) as ScoreRank
  FROM 
  (
    SELECT value as tag, p.OwnerUserId, COUNT(p.OwnerUserId) as answerCount, Sum(p.score) as answerScore
    FROM Users u
    JOIN Posts p ON p.OwnerUserId = u.Id
    INNER JOIN Posts q on q.id = p.parentid
    CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(q.tags, '><', ','), '<', ''), '>', ''), ',')
    WHERE
        LOWER(u.Location) LIKE @location
        AND p.posttypeid = 2 -- just answers
        AND value IN (
        SELECT B.value
          FROM Users iu
          JOIN Posts ip ON ip.OwnerUserId = iu.Id
          INNER JOIN Posts iq on iq.id = ip.parentid
          CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(iq.tags, '><', ','), '<', ''), '>', ''), ',') B
          WHERE
              LOWER(iu.Location) LIKE @location
              AND ip.posttypeid = 2 -- just answers
              AND ip.OwnerUserId = @userId 
          GROUP BY B.value, ip.OwnerUserId
        ) 
    GROUP BY value, p.OwnerUserId
  ) R
) V
WHERE V.OwnerUserId = @userId
--WHERE ScoreRank = 1
ORDER BY V.answerScore DESC, V.ScoreRank, V.tag

Enter Parameters

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