0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Joomla! administrators, users, developers and designers

select u.Location, count(distinct u.id) as ONIM,year(p.CreationDate) as UsageYear 
into #tempo
from Posts p inner join Posts p1 on p.id=p1.parentid inner join Users u on p1.OwnerUserId=u.id
where  p.Tags like '%python%' and year(p.CreationDate)=2017 and u.location is not null and NOT u.Location=' '
group by u.location,year(p.CreationDate)
order by ONIM desc


select u.Location,count(distinct u.id) as SHOALIM,year(p.CreationDate) as UsageYear
into #tempo1
from Posts p inner join Users u on p.owneruserid=u.id
where  p.Tags like '%python%' and year(p.CreationDate)=2017 and u.location is not null 
group by u.Location,year(p.CreationDate)
order by SHOALIM desc

select p1.owneruserid, sum(p1.score) as scoreOfTag,count(p1.id) as CountOfPostsTag
into #temp
from Posts p inner join Posts p1 on p.id=p1.parentid inner join Users u on p1.owneruserid=u.id 
where p.tags like '%python%' and u.Location is not null and year(p.CreationDate)=2017
group by p1.owneruserid

select u.location ,count(owneruserid) as Counter
into #temp12
from #temp T inner join users U on T.owneruserid=U.id
where u.Location is not null
group by u.location
order by Counter desc


select t1.Location,ONIM+SHOALIM as Counter, ONIM as CountAnswer, SHOALIM as CountQuestion , t12.Counter as TotalUsers ,t1.UsageYear
from #tempo t inner join #tempo1 t1 on t.Location=t1.Location inner join #temp12 t12 on t.location= t12.location

fork query

edited apr 18 20

created apr 18 20 anonymous