0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

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)

SELECT COUNT(Id) AS [Total Users],
       MIN(Reputation) AS [Minimum],
       MAX(Reputation) AS [Maximum],
       SUM(Reputation) / COUNT(Id) AS [Mean],
       (
           SELECT TOP 1 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
           GROUP BY Reputation
           ORDER BY COUNT(Reputation) DESC
       ) AS [Mode],
       (
           SELECT
           (
               (
                   SELECT MAX(Reputation)
                   FROM
                   (
                       SELECT TOP 50 PERCENT 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
                       ORDER BY Reputation
                   ) AS [Bottom Half]
               )
               +
               (
                   SELECT MIN(Reputation)
                   FROM
                   (
                       SELECT TOP 50 PERCENT 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
                       ORDER BY Reputation DESC
                   ) AS [Top Half]
               )
           ) / 2
       ) AS [Median],
       STDEVP(Reputation) AS [Standard Deviation]
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
    AND Reputation < 90000

fork query

edited aug 4 20

created aug 3 20 anonymous