0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

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=4190 or pt.TagId=67719 or pt.TagId=81490 or pt.TagId=20400 or pt.TagId=82977) 
and p.PostTypeid=1
and p.CreationDate>=cast('2012-07-01' as datetime) 
and p.CreationDate<=cast('2015-03-01' 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)

fork query

edited sep 17 19

created sep 17 19 anonymous