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

0

Please login or register to vote for this query.

(click on this box to dismiss)

Literature

Q&A for scholars and enthusiasts of literature

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