0

Please login or register to vote for this query.

(click on this box to dismiss)

Determines the combined reputation of all users, excluding the specified top percent

Q&A for SharePoint enthusiasts

-- Combined Reputation of Bottom x% of Users
-- Enter Query Description

WITH Rankings AS (
  SELECT
    Id,
    Reputation,
    Ranking = ROW_NUMBER() OVER(ORDER BY Reputation DESC)
  FROM
    Users
)
, Counts AS (
  SELECT
    Count = COUNT(*)
  FROM
    Users
), RankedUsers AS (
  SELECT
    Reputation, CAST(Ranking AS decimal(10, 5)) / (SELECT Count FROM Counts) AS Percentile
  FROM
    Rankings
)


SELECT
  SUM(Reputation) AS "Combined Reputation",
  COUNT(*) AS "# Users Represented"
FROM
  RankedUsers
WHERE
  Percentile > ##ExcludingTopPercent## / 100.0

fork query

created dec 15 10 Tim Stone