2

Please login or register to vote for this query.

(click on this box to dismiss)

Calculates the percentiles of users by reputation, overall. The percentiles show the lower minimal value for each percentile. E.g. to be in the top 5% of users, you must have at least the reputation as shown in the p05 column. The p001 and p005 show the the top 0.1% and 0.5% of reputation.

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

WITH top_users(reputation)
AS (
  SELECT 
    reputation
  FROM 
    users
  WHERE
    reputation > 200
)
    
    
select top 1
  percentile_disc(0.001) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p001,
  percentile_disc(0.005) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p005,
  percentile_disc(0.01) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p01,
  percentile_disc(0.05) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p05,
  percentile_disc(0.10) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p10,
  percentile_disc(0.20) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p20,
  percentile_disc(0.30) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p30,
  percentile_disc(0.40) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p40
from top_users

view current version

edited jun 19 miraculixx

created jun 19 anonymous