0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

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 '%javascript%' and year(p.CreationDate)=2016 and u.location is not null
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 '%javascript%' and year(p.CreationDate)=2016 and u.location is not null
group by u.Location,year(p.CreationDate)
order by SHOALIM desc


select t1.Location,ONIM+SHOALIM as Counter,t1.UsageYear
into #temp11
from #tempo t inner join #tempo1 t1 on t.Location=t1.Location
order by Counter desc









select u.Location,count(p1.id) as CountAnswer
into #temp1
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 '%javascript%' and year(p.CreationDate)=2016 and u.Location is not null
group by u.Location


select u.Location,count(p.id) as CountQuestion
into #temp2
from Posts p inner join Users u on p.OwnerUserId=u.id
where  Tags like '%javascript%' and year(p.CreationDate)=2016 and u.Location is not null
group by u.Location



select t2.Location,t1.CountAnswer,t2.CountQuestion
into #temp22
from #temp1 t1 inner join #temp2 t2 on t1.location=t2.location

select u.Location, count(distinct u.id) as ONIM 
into #temp111
from Posts p inner join Posts p1 on p.id=p1.parentid inner join Users u on p1.OwnerUserId=u.id
where   year(p.CreationDate)=2016 and u.location is not null
group by u.location
order by ONIM desc



select u.Location,count(distinct u.id) as SHOALIM
into #temp222
from Posts p inner join Users u on p.owneruserid=u.id
where  year(p.CreationDate)=2016 and u.location is not null
group by u.Location
order by SHOALIM desc


select t1.Location,ONIM+SHOALIM as TotalUsers
into #temp123
from #temp111 t inner join #temp222 t1 on t.Location=t1.Location
order by TotalUsers desc



select t11.Location,t11.Counter,t123.TotalUsers,t22.CountAnswer,t22.CountQuestion,t11.UsageYear
from #temp11 t11 inner join #temp22 t22 on t11.location=t22.location inner join #temp123 t123 on t11.Location=t123.Location
order by t22.CountQuestion desc

fork query

edited mar 31

created mar 31 anonymous