Find developers by tag and location

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

SELECT
    t.OwnerUserId,
    t.AboutMe,
    t.Reputation,
    SUM(t.Score) AS FinalScore
FROM (
    SELECT
        Posts.OwnerUserId,
        Users.AboutMe,
        Users.Reputation,
        CASE TagName 
        WHEN 'javascript' THEN 1.0 * COUNT(*)
          WHEN 'css'        THEN 0.8 * COUNT(*)
          WHEN 'html5'      THEN 0.8 * COUNT(*)
          WHEN 'php'        THEN 0.4 * COUNT(*)
          WHEN 'django'     THEN 0.6 * COUNT(*) 
          WHEN 'ajax'       THEN 1.0 * COUNT(*)
          WHEN 'jquery'     THEN 1.0 * COUNT(*)
          WHEN 'html'       THEN 0.8 * COUNT(*)
          ELSE 0.5 * COUNT(*)
        END as Score
    FROM Tags
        INNER JOIN PostTags ON PostTags.TagId = Tags.id
        INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
        INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2
        INNER JOIN Users ON Users.Id = Posts.OwnerUserId
    WHERE 
        TagName  IN ('css','html5','javascript','php','django','ajax','jquery','html')
        AND upper(Users.Location) like upper('%##location##%')
        AND (
        Users.AboutMe like '%http%' 
        OR upper(Users.AboutMe) like upper('%mail%') 
        OR Users.AboutMe like '%@%'
        OR upper(Users.AboutMe) like upper('%at%')
        )
    GROUP BY TagName, Posts.OwnerUserId, Users.Reputation, Users.AboutMe  
) t
GROUP BY t.OwnerUserId, t.Reputation, t.AboutMe
ORDER BY FinalScore DESC

Enter Parameters

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