I have recently forked/rewritten a SEDE query (here) that aims at figuring out where a site stands in terms of avid users and distribution of reputation scores, compared to a specific target (number of avid users in each major rep cluster).
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 /* only look at users that visited the site recently */ declare @lastActiveDays int set @lastActiveDays = 60 select [20K], [10K], [3K], [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 ( select round(sum([20K])/@target_20k, 2) [20K], round(sum([10K])/@target_10k,2) [10K], round(sum([3K])/@target_3k,2) [3K], round(sum([2K])/@target_2k,2) [2K], sum(Reputation) [TotalSiteRep], count(*) [UsersCount], sum(AvidRep) [AvidUsersRep], sum(AvidUser) [AvidUsersCount] from ( select Reputation, case when Reputation >= 20000 then 1 else 0 end [20K], case when Reputation >= 10000 then 1 else 0 end [10K], case when Reputation >= 3000 then 1 else 0 end [3K], case when Reputation >= 2000 then 1 else 0 end [2K], case when Reputation >= 150 then Reputation else 0 end [AvidRep], case when Reputation >= 150 then 1 else 0 end [AvidUser] from Users where datediff(d, LastAccessDate, getdate()) <= @lastActiveDays ) q ) r
I'm not crazy about the
case when ... then .. else ... end part and about the sub-querying, but it seems to get the job done.
Is there a better way to aggregate conditional sums? (feel free to review everything else, ...except the broken indentation of the
q query - that's already something I know I have to fix ;)
Here are the query's results for Code Review, as of the latest SEDE update: