0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

-- Top Users by location and tag (see http://stackoverflow.com/tags)
-- Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)
-- Modified by rafael-ortega
-- Re-Modified by Jessica.Sanchez
with AnswerScores AS
(
  SELECT
    Users.Id AS [User Link],
    SUM(Answers.Score) AS Score
  FROM Posts AS Answers 
    INNER JOIN Posts AS ParentQuestions 
    ON ParentQuestions.Id = Answers.ParentId
    
    INNER JOIN Users AS Users
    ON Answers.OwnerUserId = Users.Id
    
    INNER JOIN PostTags 
    ON PostTags.PostId = Answers.ParentId
    
    INNER JOIN Tags
    ON Tags.Id = PostTags.TagId
  WHERE
    LOWER(Location) LIKE LOWER('%##Location##%')
    and Tags.Tagname='##Skill1##'
  GROUP BY
    Users.Id
),

AnswerScores1 AS
(
  SELECT
    Users.Id AS [User Link],
    SUM(Answers.Score) AS Score1
  FROM Posts AS Answers 
    INNER JOIN Posts AS ParentQuestions 
    ON ParentQuestions.Id = Answers.ParentId
    
    INNER JOIN Users AS Users
    ON Answers.OwnerUserId = Users.Id
    
    INNER JOIN PostTags 
    ON PostTags.PostId = Answers.ParentId
    
    INNER JOIN Tags
    ON Tags.Id = PostTags.TagId
  WHERE
    LOWER(Location) LIKE LOWER('%##Location##%')
    and Tags.Tagname='##Skill2##'
  GROUP BY
    Users.Id
),
AnswerScores2 AS
(
  SELECT
    Users.Id AS [User Link],
    SUM(Answers.Score) AS Score2
  FROM Posts AS Answers 
    INNER JOIN Posts AS ParentQuestions 
    ON ParentQuestions.Id = Answers.ParentId
    
    INNER JOIN Users AS Users
    ON Answers.OwnerUserId = Users.Id
    
    INNER JOIN PostTags 
    ON PostTags.PostId = Answers.ParentId
    
    INNER JOIN Tags
    ON Tags.Id = PostTags.TagId
  WHERE
    LOWER(Location) LIKE LOWER('%##Location##%')
    and Tags.Tagname='##Skill3##'
  GROUP BY
    Users.Id
)
SELECT 
  TOP 100
  Users.DisplayName,
  ISNULL(AnswerScores.[User Link], AnswerScores1.[User Link]) as [User Link],
  ISNULL(AnswerScores.Score, 0) as [ ##Skill1## Score],
  ISNULL(AnswerScores1.Score1, 0) as [##Skill2## Score],
  ISNULL(AnswerScores2.Score2, 0) as [##Skill3## Score],
    Users.Reputation
FROM
  AnswerScores
FULL JOIN AnswerScores1
  ON AnswerScores.[User Link] = AnswerScores1.[User Link]
INNER JOIN Users
  ON AnswerScores.[User Link] = Users.Id
INNER JOIN AnswerScores2
  ON AnswerScores.[User Link] = AnswerScores2.[User Link]
Where 
  Users.Reputation >= '##Reputation##'

fork query

edited jun 29 22 anonymous

created jun 29 22 Jess.Dubon