Q&A about the site for linguists, teachers and students of the Ukrainian language
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, /*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=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=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=122933 or pt.TagId=117381 or pt.TagId=122685 or pt.TagId=132868) and p.PostTypeid=1 and p.CreationDate>=cast('2012-07-01' as datetime) and p.CreationDate<=cast('2015-08-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 /*where userlist.uid=p.OwnerUserId and p.CreationDate>=cast('2013-09-18' as datetime) and p.CreationDate<=cast('2014-03-24' as datetime) and p.PostTypeId=1 and p.ViewCount>100 and p.Id = pt.PostId and pt.TagId = t.Id and (pt.TagId=1386 or pt.TagId = 58338) */ ) 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)