Aggregating Conditional Sums
It's a good habit to end each statement with an explicit semicolon.



The innermost query q generates one row for each recent user.  Since you have no interest in individual users, and only want counts and sums, you can collapse the two innermost queries q and r — both poorly named, by the way — into one.  Use COUNT() for counting.



The lastActiveDays filtering is being done inefficiently:

where datediff(d, LastAccessDate, getdate()) &lt;= @lastActiveDays


To accomplish that, the server would have to run datediff() on every single row in the Users table.  Instead, you should calculate the cutoff date just once:

where LastAccessDate &gt;= cast(dateadd(day, -@lastActiveDays, getdate()) as Date)


(The cast truncates the result to midnight.)



I prefer to use Common Table Expressions for readability.  Since you are not running a correlated subquery, a top-down layout is better than nesting.

The UserStats CTE below is roughly equivalent to subquery q.  I've deferred the … / @target… divisions for readability and rearranged the columns in a more logical order.  It could be written as just one SELECT, but I think that splitting it up with a CTE makes it easier to understand.



declare @target_20k float;
declare @target_10k float;
declare @target_3k float;
declare @target_2k float;

set @target_20k = 3.0;
set @target_10k = 10.0;
set @target_3k = 80.0;
set @target_2k = 120.0;

declare @lastActiveDays int;
set @lastActiveDays = 60;

with UserStats as (
  select
      count(case when Reputation &gt;= 20000 then 1 else null end) [20KUsersCount],
      count(case when Reputation &gt;= 10000 then 1 else null end) [10KUsersCount],
      count(case when Reputation &gt;=  3000 then 1 else null end) [3KUsersCount],
      count(case when Reputation &gt;=  2000 then 1 else null end) [2KUsersCount],
      count(case when Reputation &gt;=   150 then 1 else null end) [AvidUsersCount],
      count(Id) [UsersCount],
      sum(case when Reputation &gt;= 150 then Reputation else 0 end) [AvidUsersRep],
      sum(Reputation) [TotalSiteRep]
    from Users
    where LastAccessDate &gt;= cast(dateadd(day, -@lastActiveDays, getdate()) as Date)
)
select
    round([20KUsersCount] / @target_20k, 2) [20K],
    round([10KUsersCount] / @target_10k, 2) [10K],
    round([3KUsersCount]  / @target_3k,  2) [3K],
    round([2KUsersCount]  / @target_2k,  2) [2K],
    [TotalSiteRep],
    [UsersCount],
    round([TotalSiteRep] / cast([UsersCount] as float), 2) [AvgRep],
    [AvidUsersRep],
    round([AvidUsersRep] / cast([TotalSiteRep] as float), 2) [%AvidRep],
    [AvidUsersCount],
    round([AvidUsersCount] / cast([UsersCount] as float), 2) [%AvidUsers],
    round([AvidUsersRep] / cast(AvidUsersCount as float), 2) [AvgAvidRep]
  from UserStats;