0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Russian language

select top 10 u.DisplayName, u.Id, count(p_sql.TagName), count(p_java.TagName)
from Users u
inner join (
  select p.Id, p.OwnerUserId, t.TagName from Posts p 
  inner join PostTags pt on p.Id=pt.PostId
  inner join Tags t on pt.TagId=t.Id
  where p.PostTypeId=1 and t.TagName='sql'
  ) p_sql on p_sql.OwnerUserId=u.Id
inner join (
  select p.ParentId, p.OwnerUserId, t.TagName from Posts p 
  inner join PostTags pt on p.ParentId=pt.PostId
  inner join Tags t on pt.TagId=t.Id
  where p.PostTypeId=2 and t.TagName='java'
  ) p_java on p_java.OwnerUserId=u.Id
group by u.DisplayName, u.Id
having count(p_sql.TagName) <= 3 and count(p_java.TagName) >= 10


/*
select top 10 u.DisplayName, u.Id, t.TagName 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 and t.TagName='sql2')
where p.PostTypeId=2
--(p.PostTypeId=2) --and t.TagName='java')
--group by u.DisplayName, u.Id, t.TagName
--having count(*) >= 3
*/

fork query

edited dec 18 17

created dec 18 17 anonymous