0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

;with all_loc as (
select  u.location
, count(*) as per_country
from users u 
where u.location is not null
and u.location <> ''
group by u.location
)
, badge_loc as (
select  u.location
, count(*) as [badges]
from users u 
where exists (select 1 
              from badges 
              where userid = u.id 
              and name ='java')
and u.location is not null
 and u.location <>''
group by u.location 
)
select a.location
     , a.per_country
     , b.badges
     , cast(b.badges as decimal) 
     / cast(a.per_country as decimal) * 100 as perc
     , (select count(*) 
        from users 
        where location is not null
        and location <> '') as tot_count
from all_loc a
inner join badge_loc b on b.location = a.location

fork query

edited mar 29 15

created mar 29 15 rene