0

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.

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.00) WITHIN GROUP (ORDER BY reputation desc) OVER() AS p000,
  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

fork query

edited jun 19 miraculixx

created jun 19 anonymous