0

Please login or register to vote for this query.

(click on this box to dismiss)

Only tags used in at least ten questions

Q&A for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

-- Tags with most/least questions per user
-- Only tags used in at least ten questions

with ratio as (
select
  t.TagName            as [Tag],
  count(distinct u.Id) as [Users],
  count(distinct p.Id) as [Questions],
  round(count(distinct p.Id) * 1.0 / count(distinct u.Id),2)
                       as [Average Number of Questions per User]
from
   users    u  inner join
   posts    p  on u.Id = p.OwnerUserId
               inner join
   posttags pt on p.Id = pt.PostId
               inner join
   tags     t  on pt.TagId = t.Id
where p.PostTypeId = 1
group by t.Id, t.TagName
having count(distinct p.Id) >= 10
)

select * from (
  select top 50
    *
  from ratio
  order by [Average Number of Questions per User], [Questions] desc
union
  select top 50
    *
  from ratio
  order by [Average Number of Questions per User] desc, [Questions] desc
) sq
order by [Average Number of Questions per User] desc, [Questions] desc;

fork query

created aug 19 10 sth