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=19195) 
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=4190 or pt.TagId=67719 or pt.TagId=81490 or pt.TagId=20400 or pt.TagId=82977 or pt.TagId=5990)
) 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