I've created the following SEDE query which calculates the reputation averages of users on a particular site. It also optionally excludes "new" users (users with rep of 1 or 101 so note 100% accurate).
DECLARE @exclude_new_users INT
SET @exclude_new_users = ##ExcludeNewUsers:int?0##
DECLARE @excluded_rep_table TABLE (Rep INT)
INSERT INTO @excluded_rep_table VALUES (1),(101)
DECLARE @rep_table TABLE (Reputation INT)
INSERT INTO @rep_table (Reputation)
SELECT Reputation
FROM USERS
WHERE Reputation <>
CASE WHEN @exclude_new_users = 0
THEN -1
ELSE (SELECT IIF (EXISTS (SELECT Rep FROM @excluded_rep_table WHERE Rep = Reputation), Reputation, -1))
END
SELECT COUNT(*) AS [Total Users],
MIN(Reputation) AS [Minimum],
MAX(Reputation) AS [Maximum],
SUM(Reputation) / COUNT(*) AS [Mean],
(
SELECT TOP 1 Reputation
FROM @rep_table
GROUP BY Reputation
ORDER BY COUNT(Reputation) DESC
) AS [Mode],
(
SELECT
(
(
SELECT MAX(Reputation)
FROM
(
SELECT TOP 50 PERCENT Reputation
FROM @rep_table
ORDER BY Reputation
) AS [Bottom Half]
)
+
(
SELECT MIN(Reputation)
FROM
(
SELECT TOP 50 PERCENT Reputation
FROM @rep_table
ORDER BY Reputation DESC
) AS [Top Half]
)
) / 2
) AS [Median],
STDEVP(Reputation) AS [Standard Deviation]
FROM @rep_table
I'm quite the SQL novice and mainly did this for some practice so I'm really after general feedback: ways to improve/optimise the code but also to improve the layout, it looks ugly and I can't imagine it fits with style guidelines.
