Announcing our $3.4M seed round 🚀 Read more →

How to Calculate Percentiles in Snowflake in Snowflake

Let's say you want to look at the percentiles for products. You can use Snowflake's percentile_cont() function to do that:

select 
  percentile_cont(0.25) within group(order by unit_price) over () as p25,
  percentile_cont(0.50) within group(order by unit_price) over () as p50,
  percentile_cont(0.75) within group(order by unit_price) over () as p75,
  percentile_cont(0.95) within group(order by unit_price) over () as p95
from products;

If you want to view those percentiles by category:

select distinct category,
  percentile_cont(0.25) within group(order by unit_price) over (partition by category) as p25,
  percentile_cont(0.50) within group(order by unit_price) over (partition by category) as p50,
  percentile_cont(0.75) within group(order by unit_price) over (partition by category) as p75,
  percentile_cont(0.95) within group(order by unit_price) over (partition by category) as p95
from products
order by category;

Ready for a modern SQL editor?