Postgres: select all records in 95th percentile

Question

I'm trying to query all rows which are in the 95th percentile. Here's what I tried.

select
  id,
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage
where usage > percentile_95

Answer 1

Your query fails with ERROR: column "percentile_95" does not exist which is caused by the fact that you can't reference in the WHERE clause column alias from the SELECT.

If you remove the WHERE clause you get a different error

select
  id,
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage

ERROR: column "resourceusage.id" must appear in the GROUP BY clause or be used in an aggregate function

Which points you how to calculate the percentile_cont for the whole table (i.e. without using GROUP BY):

select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage;

percentile_95             
------------------------- 
95.05 

Now you are close to the result, get the above result in a subquery and use it in the WHERE clause.

select
  id,
  usage
from ResourceUsage
where usage > 
 (select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
 from ResourceUsage);