I have a table that contains "id" and "score".
id | score
1 | 10
2 | 20
3 | 30
4 | 40
How can I find the percentiles of scores using PostgreSQL to get an expected output of:
id | score | percentile
1 | 10 | 25.0
2 | 20 | 50.0
3 | 30 | 75.0
4 | 40 | 100.0
In python one way to solve this would be using scipy.stats.percentileofscore
>>>from scipy import stats
>>>stats.percentileofscore([1, 2, 3, 4], 3)
75.0
but I'd like a way to do this with PostgreSQL
I tried using PERCENT_RANK,
"%(function)s() OVER (ORDER BY %(expressions)s DESC) *100"
But it has not returned the same value as scipy.stats.percentileofscore