0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for scientists using computers to solve scientific problems

select qcomplex.userId as userId, avg(qcomplex.maxfrequency) as maxfrequency, AVG(qcomplex.minfrequency) as minfrequency, avg(qcomplex.sumfrequency) as sumfrequency,
AVG(qcomplex.avgfrequency) as avgfrequency, avg(qcomplex.sumrarity) as sumrarity, avg(qcomplex.avgrarity) as avgrarity, datediff(month,cast('2015-03-01' as datetime), qcomplex.qdate) as month

from 
(select max(qlist.uid) as userId, max(tagfreq.tagfrequency) as maxfrequency, min(tagfreq.tagfrequency) as minfrequency, sum(tagfreq.tagfrequency) as sumfrequency,
 AVG(tagfreq.tagfrequency) as avgfrequency,  sum(tagfreq.tagrarity) as sumrarity, AVG(tagfreq.tagrarity) as avgrarity, max(qlist.qdate) as qdate

from PostTags pt,
/* (select t.Id as tid, cast(t.Count as numeric) as tagfrequency, (1/log(cast(t.Count as numeric))) as tagrarity */  /*  results no good*/
(select t.Id as tid, cast(t.Count as numeric) as tagfrequency, (1/cast(t.Count as numeric)) as tagrarity
from Tags t
where t.Count>1
) tagfreq,

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

from PostsWithDeleted p, PostTags pt, 
/*from PostsWithDeleted p, PostTags pt, Tags t, */
(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=19195) 
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 pt.PostId = p.Id
and (pt.TagId=19195 or pt.TagId=2751)
) qlist

where tagfreq.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 oct 15 19

created oct 11 19 anonymous