All User Tag Ranks by Location

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow Meta en español

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

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

SELECT R.tag, R.OwnerUserId, RANK() over (partition by R.OwnerUserId order by R.answerCount desc) as Rank
FROM 
(
  SELECT value as tag, p.OwnerUserId, COUNT(p.OwnerUserId) as answerCount
  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
WHERE R.OwnerUserId = @userId 
ORDER BY Rank, R.tag

Enter Parameters

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