Nominees activity in tags that moderators are not.

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

with TopTags as (
select top 50 TagId, count(*) as ct
  from PostTags
 group by TagId
 order by count(*) desc
 )
 , UserTags as (
select pt.TagId, p.OwnerUserId, count(*) as ct
  from Posts p
  join PostTags pt
    on coalesce(p.ParentId, p.Id) = pt.PostId
 where p.PostTypeId in (1, 2)
   and p.CreationDate > GETDATE() - 365
 group by pt.TagId, p.OwnerUserId
       )
 , NomineeTags as (
 select tt.TagID, t.TagName
   from TopTags tt
   join Tags t
     on tt.TagId = t.Id
   left outer join ( select TagId, sum(ct) as ct
                       from UserTags
                      where OwnerUserId in ( 1288, 54680, 13531
                            , 1228, 102937, 50049, 267, 106224, 396458
                            , 50776, 2598 )
                      group by TagID
                     having sum(ct) > 30
                         ) as mods
     on tt.TagID = mods.TagID
   left outer join ( select TagId, sum(ct) as ct
                       from UserTags
                      where OwnerUserId in ( 92837, 996493, 109749
                            , 558021, 561731, 237838, 16587, 19679
                            , 109702, 707111, 246246, 168175, 225647 )
                      group by TagId
                      having sum(ct) > 30
                         ) as nominees
     on tt.TagId = nominees.TagId
  where mods.TagId is null
    and nominees.TagId is not null
    )
    select distinct '[' + u.DisplayName + ']' + '(http://stackoverflow.com/users/' + cast(u.Id as char(6)) + ')'
     , '[tag:' + nt.TagName + ']'
  from Users u
  join UserTags ut
    on u.Id = ut.OwnerUserID
  join NomineeTags nt
    on nt.TagId = ut.TagID
 where u.id in ( 92837, 996493, 109749
               , 558021, 561731, 237838, 16587, 19679
               , 109702, 707111, 246246, 168175, 225647 )
   and ut.ct > 30

Enter Parameters

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