CSCW tech specific - disaggregated

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

-- young/vet by tech-specific/agnostic
select tag_cat, sum(rep_tot) rep_tot, sum(score_tot) score_to, sum(users_tot) users_tot, age_cat
from 
-- technology-specific
(select 
'technology-specific' as tag_cat,
users.age age, 
sum(users.reputation) rep_tot, 
sum(posts.score) score_tot,
count(posts.owneruserid) users_tot,
CASE
  WHEN age >= 15 and age <= 39 THEN 'younger'
  WHEN age > 39 and age <= 70 THEN 'veteran'
  END as age_cat
from users, posts, posttags, tags 
where
users.age is not null and users.age between 15 and 70
and users.reputation between 2 and 100000 
and year(posts.creationdate)=2012
and posts.owneruserid=users.id
and posts.id = posttags.postid
and posttags.tagid=tags.id
and tags.tagname 
in ('c#','java','php','javascript','android','jquery','c++','python','iphone','html')
group by users.age
union
-- technology-agnostic
select 
'technology-agnostic' as tag_cat,
users.age age, 
sum(users.reputation) rep_tot, 
sum(posts.score) score_tot,
count(posts.owneruserid) users_tot,
CASE
  WHEN age >= 15 and age <= 39 THEN 'younger'
  WHEN age > 39 and age <= 70 THEN 'veteran'
  END as age_cat
from users, posts, posttags, tags 
where
users.age is not null and users.age between 15 and 70
and users.reputation between 2 and 100000 
and year(posts.creationdate)=2012
and posts.owneruserid=users.id
and posts.id = posttags.postid
and posttags.tagid=tags.id
and tags.tagname 
in ('regex', 'database','arrays','string','multithreading','image','performance','algorithm','query','api')
group by users.age
) X group by age_cat, tag_cat

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results