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] , u.creationDate 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: