select qcomplex.userId as userid, avg(qcomplex.complexity...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

select qcomplex.userId as userid, avg(qcomplex.complexitysum) as complexitysum, AVG(qcomplex.complexity) as complexity, avg(qcomplex.varcomplexity) as varcomplexity,
avg(qcomplex.tagcount) as tagcount, datediff(month,cast('2015-03-01' as datetime), qcomplex.qdate) as month

from 

(select max(qlist.uid) as userId, sum(tagcomplexity.complexity) as complexitysum, AVG(tagcomplexity.complexity) as complexity, var(tagcomplexity.complexity) as varcomplexity, count(pt.TagId) as tagcount, max(qlist.qdate) as qdate

from PostTags pt,

(select t.Id as tid, sum( posttagm1.tagc)as countallJ, min(t.Count) as countI, (sum( posttagm1.tagc)/cast(min(t.Count) as numeric)) as complexity

from Tags t, PostTags pt,

(select pt.PostId as pid, (count(pt.TagId)-1) as tagc
from PostTags pt
group by pt.PostId) posttagm1 

where t.Id=pt.TagId and pt.PostId=posttagm1.pid
group by t.Id
) tagcomplexity,

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

from Posts p,

(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=5990) 
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

) qlist

where tagcomplexity.tid=pt.TagId and pt.PostId=qlist.pid
group by qlist.pid
) qcomplex

group by qcomplex.userId, datediff(month,cast('2015-03-01' as datetime), qcomplex.qdate)

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results