CODEWITHSUNDEEP
sqlsnowflake-schema
User
User

Reputation: 57

Calculate percentiles using SQL for a group/partition

I want to calculate the percentiles for a given partition/group in SQL. For example the input data looks like -

CustID     Product ID     quantity_purchased    
1          111                2
2          111                3
3          111                2 
4          111                5
1          222                2
2          222                6
4          222                7
6          222                2

I want to get percentiles on each product ID group. The output should be -

Product ID    min      25%      50%      75%     max
    111        2        2       2.5      3.5      5
    222        2        2        4       6.25     7

How to achieve this using SQL?

Upvotes: 0

Views: 2583

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can use percentile_cont():

select product_id, min(quantity_purchased), max(quantity_purchased),
       percentile_cont(0.25) within group (order by quantity_purchased),
       percentile_cont(0.50) within group (order by quantity_purchased),
       percentile_cont(0.75) within group (order by quantity_purchased)
from t
group by product_id;

Upvotes: 2

Related Questions