4

Please login or register to vote for this query.

(click on this box to dismiss)

The users and how many tags they have with more than 15 score in the top 40. .... need 20 tags with score >= 15 in order to qualify for Generalist.

Q&A about the site for serious players and enthusiasts of chess

with tags40 as (
  select top 40 TagId from posttags group by tagid order by count(*) desc
), answerers as (
  select OwnerUserId as UserId
  from posts, users
  where PostTypeId = 2
    and posts.OwnerUserId = users.Id
    and users.Reputation > 200
  group by OwnerUserId
  having count (*) >= 4
), tagsoup as (
  select u.UserId, t.TagId, sum(case when VoteTypeId = 2 then 1 else -1 end) as [TagScore]
  from answerers u,
       posts a,
       posts q,
       votes v,
       tags40 t,
       PostTags pt
  where u.UserId = a.OwnerUserId
    and a.ParentId = q.Id
    and a.parentId = pt.PostId
    and pt.TagId = t.TagId
    and a.Id = v.PostId
    and v.VoteTypeId in (2, 3)
    and a.CommunityOwnedDate is null
    and q.CommunityOwnedDate is null
  group by u.UserId, t.TagId
), userswith as (
  select UserId, count (*) as TagCount
  from TagSoup
  where TagScore >= 15
  group by UserId
)

select ROW_NUMBER() Over (order by TagCount Desc, Reputation Desc) as Rank,
       userId as [User Link],
       DisplayName as [User Name],
       Reputation as [Reputation],
       TagCount
from userswith, Users
where users.id = userId
order by tagcount desc

fork query

edited may 6 14

created jan 31 14 rolfl