User Tag Ranks by Location


Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

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

    , V.AnswerRank as 'Rank By # of Answers'
    , V.ScoreRank as 'Rank By Answer Score'
    , V.answerScore as 'Total Answer Score'
    , V.answerCount as '# of Answers'
  SELECT R.tag
    ,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
    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 = p.parentid
    CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(q.tags, '><', ','), '<', ''), '>', ''), ',')
        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 = ip.parentid
          CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(REPLACE(iq.tags, '><', ','), '<', ''), '>', ''), ',') B
              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

Switch to main site
loading Hold tight while we fetch your results