Top SO users by tech stack (tags score) and location


Please login or register to vote for this query.

(click on this box to dismiss)

Latin Language

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language


Finds top StackOverflow users by their technology stack and location
Usage example: find experts in REST, Spring, other Spring frameworks 
               and Hibernate related projects from Ukraine, 
               with minimum StackOverflow reputation 1000

  Tags:            rest, spring
  Tag masks:       spring-, hibernate-
  Location masks:  Ukraine
  Min. reputation: 1000


  - Tag is an exactly StackOverflow tag related to the particular technology, 
    for example: 'java', 'java-8' or 'spring-data-jpa'.
    List of all tags can be found here:
  - Tag mask here is the beginning part of the tag name. There are a lot of tags
    related to the one main technology and started identically with a dash, 
    for example: 'spring-boot', 'spring-data', 'spring-data-jpa' - all those
    tags have tag mask 'spring-'.
    So if you want to find experts only in Spring - you should only type 'spring' 
    in the field 'Tags'. But if you need to find experts in Spring and related
    frameworks as well, you should type 'spring' in 'Tags' and 'spring-' in 'Tag masks'.

  - Location masks are arbitrary parts of the user location separated by a comma,
    for example: 'Odessa, Odesa' or 'Kiev, Kyiv'.
  - Min. reputation is a minimum reputation of StackOverflow user who will get in the selection.


declare @tags table (
  tag varchar(255) collate SQL_Latin1_General_CP1_CS_AS

declare @tag_masks table (
  mask varchar(255) collate SQL_Latin1_General_CP1_CS_AS

declare @locations table (
  mask varchar(255) collate SQL_Latin1_General_CP1_CS_AS

insert into @tags select trim(value) from string_split(lower(trim(##Tags:string##)), ',');
insert into @tag_masks select trim(value) + '%' from string_split(lower(trim(##TagMasks:string? ##)), ',');
insert into @locations select '%' + lower(trim(value)) + '%' from string_split(lower(trim(##Location:string? ##)), ',');

located_users as (
    users uu
    join @locations l on lower(uu.location) like l.mask 
    uu.reputation >= ##MinReputation:int?1000##       
  row_number() over (order by sum(a.score) desc) as [#]
, as [User Link]
, sum(a.score) as Score

, (select string_agg(ttt.tag, ', ') from (
    select top 10    
      tt.tagName as tag    
      users uu
      join posts aa on aa.ownerUserId = and aa.postTypeId = 2 and aa.score > 0
      join posts qq on = aa.parentId
      join postTags ptt on ptt.postId =
      join tags tt on = ptt.tagId
    where =
    group by
    order by
      sum(aa.score) desc
    ) as ttt
  ) as [Top10 tags of the user]

, u.location as Location
, u.webSiteUrl as [Web URL] 
, u.creationDate as [creationDate] 

  located_users u
  join posts a on a.ownerUserId = and a.postTypeId = 2 and a.score > 0
  join posts q on = a.parentId
  join postTags pt on pt.postId =  
  join tags t on = pt.tagId 
, @tag_masks tm

  t.tagName in (select tag from @tags) or (trim(##TagMasks##) <> '' and t.tagName like tm.mask)

group by
, u.location
, u.webSiteUrl
, u.creationDate

order by
  sum(a.score) desc;

-- Tags: Tags:
-- TagMasks: Tag masks:
-- Location: Location masks:
-- MinReputation: Min. reputation:

Enter Parameters

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