0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for serious players and enthusiasts of chess

select p.OwnerUserId, min(cast(t.Count as numeric)) as min_tag_fix, max(cast(t.Count as numeric)) as max_tag_fix, avg(cast(t.Count as numeric)) as avg_tag_fix, sum(t.Count ) as sum_tag_fix, avg( 1 / cast(t.Count as numeric) ) as avg_rarity_fix ,
sum( 1 / cast(t.Count as numeric) ) as sum_rarity_fix 
from Tags t, PostTags pt, Posts p,

(select p.Id as pid, userlist.uid as uid, p.CreationDate as qdate

from PostsWithDeleted p, PostTags pt,

(select p.OwnerUserId as uid 
from Posts p,  PostTags pt, Users u
where p.OwnerUserId=u.Id and p.Id=pt.PostId
and ( pt.TagId=81490 or pt.TagId=82977 or pt.TagId=16712)
and p.PostTypeid=1
and p.CreationDate>=cast('2015-03-01' as datetime) 
and p.CreationDate<=cast('2017-03-31' as datetime) 
group by p.OwnerUserId
) userlist
  

where userlist.uid=p.OwnerUserId and p.CreationDate>=cast('2015-03-01' as datetime) and p.CreationDate<=cast('2017-03-31' as datetime) and p.PostTypeId=1
and p.Id=pt.PostId
and (pt.TagId=112144 or pt.TagId=115813 or pt.TagId=108695 or pt.TagId=111832 or pt.TagId=118937 or pt.TagId=124253 or pt.TagId=92230 or pt.TagId=122685 or pt.TagId=100570
or pt.TagId=40102 or pt.TagId=109466 or pt.TagId=116033 or pt.TagId=113994 or pt.TagId=24100 or pt.TagId=99347 or pt.TagId=132868)
) qlist

where pt.PostId = qlist.pid and  pt.TagId = t.Id and qlist.pid = p.Id
group by p.OwnerUserId

fork query

edited oct 13 19

created oct 11 19 anonymous