0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for serious players and enthusiasts of chess

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
        
DECLARE @rep_count BIGINT
SET @rep_count = (SELECT COUNT(*) FROM @rep_table)

SELECT @rep_count AS [Total Users],
       MIN(Reputation) AS [Minimum],
       MAX(Reputation) AS [Maximum],
       SUM(Reputation) / @rep_count AS [Mean],
       (
           SELECT TOP 1 Reputation
           FROM @rep_table
           GROUP BY Reputation
           ORDER BY COUNT(Reputation) DESC
       ) AS [Mode],
       (
           SELECT AVG(1.0 * Reputation)
           FROM
           (
               SELECT Reputation FROM @rep_table
               ORDER BY Reputation
               OFFSET (@rep_count - 1) / 2 ROWS
               FETCH NEXT 1 + (1 - @rep_count % 2) ROWS ONLY
           ) AS [Inner Median]
       ) AS [Median],
       STDEVP(Reputation) AS [Standard Deviation]
FROM @rep_table

fork query

edited aug 5

created aug 3 anonymous