Calculating 99th Percentile Value

Open Q & A Querying your data
1

I’d like to calculate the percentile value in a SQL query of a calculated column (sort by a column, and return the value at the 99th percentile) for some monitoring work in my app. Couldn’t figure out a way to do this with the SQL functions I could find. How do I do this?

1 Like
2

thanks @edwardqi - someone from engineering will respond!

3

Hi @edwardqi -

You can use window functions and use percent_rank().

Something like this might work:

data_with_percentiles as (
    select name,
           percent_rank() over(column) as p_rank
    from
        table
)
select
    name
from
    data_with_percentiles
where
    p_rank > 0.99

You can save this as a view on Rockset and then filter from there. Let me know if this helps!

n