select qcomplex.userId as userId, avg(qcomplex.maxfrequen...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

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=77858) 
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
and pt.PostId = p.Id
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 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)

Enter Parameters

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