Search developers on location and skills

0

Please login or register to vote for this query.

(click on this box to dismiss)

Bitcoin Meta

Q&A about the site for Bitcoin crypto-currency enthusiasts

-- Top Users by location and tag (see http://stackoverflow.com/tags)
-- Created by samliew (http://stackoverflow.com/users/584192/samuel-liew)
-- Modified by sgryzko
-- Re-Modified by Prasad.Lingawar
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
)
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],
  Users.Reputation
FROM
  AnswerScores
FULL JOIN AnswerScores1
  ON AnswerScores.[User Link] = AnswerScores1.[User Link]
INNER JOIN Users
  ON AnswerScores.[User Link] = Users.Id
Where 
  Users.Reputation >= '##Reputation##'

Enter Parameters

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