Let's say I have two tables called schools and students which have a one-to-many relationship. Each student can only be in one school but each school can have many students.
school
-----------
id
name
student
-----------
id
name
school_id
I need to find the 90th percentile of the number of students each school has.
I can already sort the schools based on the student count, but I don't know how to get the percentile.
select school_id, count(id) as count from students
group by school_id
order by count desc
I figured out a working query which is as follows:
select percentile_cont(0.50) within group (order by schools_student_counts.count)
from (select count(id) as count from students s group by school_id order by count desc) as schools_student_counts
The downside is you have to run it once for each desired percentile. Would have been nice if we could select multiple percentile values at once.