0

Please login or register to vote for this query.

(click on this box to dismiss)

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

Clarifications:

  - 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: https://stackoverflow.com/tags
  
  - 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.

(c) https://stackoverflow.com/users/5380322/cepr0

*/
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? ##)), ',');

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

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

, u.location as Location
, u.webSiteUrl as [Web URL] 
, DATEDIFF(YEAR, u.creationDate, GETDATE()) as [creationDate]

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

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

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

order by
  sum(a.score) desc;

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

fork query

edited mar 26 21 anonymous

created jan 27 18 Cepr0