Candidates

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select 
         id as [User Link], 
         location,
         WebsiteUrl,
         tags_score,
         reputation,
         perc_tags_rep
         ,count(distinct skill_group)
FROM (
  select u.id, --as [User Link], 
         u.location,
         u.WebsiteUrl,
         CASE
         WHEN t.TagName in ('java','scala') THEN 'cs'
         WHEN t.TagName in (
           'sql','postgresql','tsql','sql-server','oracle',
           'mysql','jdbc','odbc','dbi') THEN 'sql'
         ELSE 'web'
         END skill_group,
         sum(score) tags_score,
         reputation,
         10000*sum(score)/reputation perc_tags_rep
  from Posts p, PostTags pt, Tags t, Users u,
    (select id
    from users
    where reputation > 100 and location is not null and (
      lower(location) = 'dc' OR 
      lower(location) = 'md' OR
      lower(location) = 'va' OR 
      lower(location) = '%, dc' OR 
      lower(location) = '%,dc' OR 
      lower(location) like '%, md' OR 
      lower(location) like '%,md' OR
      lower(location) like '%, va' OR 
      lower(location) like '%,va' OR
      lower(location) like '%, maryland' OR
      lower(location) like '%, virginia' OR
      lower(location) like '%,maryland' OR
      lower(location) like '%,virginia'
      )
    ) dmv
  where 1=1
  AND PostTypeId = 2 -- answer
  AND p.ParentId = pt.PostId
  AND pt.TagId = t.id
  AND p.OwnerUserId = u.id
  AND u.id = dmv.id
  --AND t.TagName = ##tag:string?python##
  AND t.TagName in (
    'java','scala',
    'sql','postgresql','tsql','sql-server','oracle','mysql','jdbc','odbc','dbi',
    'html','javascript', 'css','xml','d3','php','xhtml','jquery'
    )
  AND score > 0
  group by u.id, 
           u.WebsiteUrl,
           u.location,
           u.reputation,
           t.TagName  
) a
group by 
         id,
         location,
         WebsiteUrl, 
         tags_score,
         reputation,
         perc_tags_rep
HAVING count(distinct skill_group) > 1 -- pick two
order by 
  --perc_tags_rep 
  --reputation
  tags_score
desc

Enter Parameters

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