2

Please login or register to vote for this query.

(click on this box to dismiss)

Lists the total number of users, the total reputation, the average reputation, and site composition for categories of users on the Meta site.

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

DECLARE @TotalUsers money --Money type makes it easy to group digits
DECLARE @TotalReputation money
DECLARE @Results table(Category varchar(18), Users money, Sum_Reputation money, Avg_Reputation money, Pct_Users decimal(6,1), Pct_Reputation decimal(6,1));

SELECT @TotalUsers = COUNT(ID), @TotalReputation = SUM(Reputation) from Users

Insert @Results(Category, Users, Sum_Reputation, Avg_Reputation, Pct_Users, Pct_Reputation)
Select 'All', @TotalUsers, @TotalReputation, @TotalReputation/@TotalUsers, 100, 100
Union ALL
Select 'Rep>1', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 1
Union ALL
Select 'Rep>10', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 10
Union ALL
Select 'Rep>100', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 100
Union ALL
Select 'Rep>1,000', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 1000
Union ALL
Select 'Rep>10,000', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 10000
Union ALL
Select 'Rep>100,000', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation > 100000
Union ALL
Select 'Rep<10', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation < 10
Union ALL
Select 'Rep<100', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation < 100
Union ALL
Select 'Rep<1,000', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation < 1000
Union ALL
Select 'Rep<10,000', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation < 10000
Union ALL
Select '[10..100]', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation between 10 and 100
Union ALL
Select '[100..1,000]', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation between 100 and 1000
Union ALL
Select '[1,000..10,000]', COUNT(Id), SUM(Reputation), AVG(Convert(money,Reputation)), COUNT(ID) / @TotalUsers *100, SUM(Reputation) / @TotalReputation *100 FROM Users WHERE Reputation between 1000 and 10000


Select Category, Parsename(Convert(VarChar(50),Users,1),2) as [Users], Parsename(Convert(VarChar(50),Sum_Reputation,1),2) as [Total Rep], Convert(VarChar(50), Avg_Reputation,1) as [Avg Rep], Convert(varchar(10), Pct_Users) + '%' as [% Users], Convert(varchar(10), Pct_Reputation) + '%' as [% Rep]  from @Results

fork query

created jun 17 10 greg.bray