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, V.ScoreRank ,V.OwnerUserId FROM ( SELECT R.tag ,R.OwnerUserId ,RANK() over (partition by R.OwnerUserId order by R.answerCount desc) as AnswerRank ,RANK() over (partition by R.OwnerUserId 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.ScoreRank, V.tag